Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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'
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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.

Reply With Quote
  #4 (permalink)  
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.
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:52 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.