Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 6th, 2007, 01:41 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default AND/OR criteria in a JOIN

The following SQL statement works in SQL Server 2005. I need to use it in Access 2000 but can't get it working. I used the Query Designer in Access to get the basic tables joined, and then tried adding the AND parts to the SQL View, but get various messages depending where the () are; sometimes I get "JOIN not supported", other times it says it can't represent the query in design view and when I try running it, nothing happens; no errors, no results. It just doesn't do anything.

Does Access support AND/OR criteria in the JOIN/ON clause?

SELECT (columns)
FROM P
INNER JOIN A
ON A.col1 = P.col1
INNER JOIN AI
ON AI.col2 = A.col2
    AND AI.col3 = 1
LEFT JOIN LP
ON LP.col4 = A.col4
    AND LP.col4 = P.col4
    AND LP.col5 = 'text'
 
Old July 9th, 2007, 06:18 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Access 2000 only supports ANSI SQL 89, so not as current as SQL Server 2005, which is at least ANSI 92.

Perhaps you can build the query in the designer and create the joins visually, then configure them with the join's properties dialog box.

Did that help?


mmcdonal
 
Old July 10th, 2007, 09:35 AM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The join properties only give three options basically making it a left, right, or inner join. Looks like Access won't be able to do what I need. Thank you anyway.

 
Old July 17th, 2007, 04:03 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I were to make this query into a Stored Procedure or Function in SQL Server, would Access 2000 be able to call it somehow? Access would need to pass a user entered date as a parameter.
 
Old July 18th, 2007, 06:23 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure, but I know that if you make it a View in SQL Server, Access can call it and pass it parameters.


mmcdonal
 
Old July 18th, 2007, 02:37 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There are no "parameterized Views"; the closest you get is a Table Valued UDF. If Access can SELECT from a View however, I'd guess it can SELECT from a UDF. I'll play with it. Thanks for the help.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Best Searching Criteria Muhammad Zeeshan SQL Server 2000 1 September 30th, 2007 07:14 AM
2 LINK CRITERIA Vision G Access 1 July 14th, 2006 10:33 AM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
what is the criteria to get one record yylee Access 1 April 29th, 2004 04:19 PM
Query Criteria Clive Astley Access 4 March 25th, 2004 03:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.