Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| 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 February 14th, 2005, 05:06 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default SQL from 3 tables as source for List Box?

Could someone help me on this one, please?
I am trying to add a field from a third table to a SQL that displays data in a ListBox. It populated the listbox fine until I tried adding the third table (tblCHDPStatus).

Can one use the "AND" operator in the FROM clause? If so, did I do that wrong? This statement throws a "error in FROM" clause error.

SELECT [tblPractice].[LegalName], [tblCHDPStatus].[CHDP Status], [tblCity].[City], [tblPractice].[PracticeNum] FROM (tblCHDPStatus INNER JOIN tblPractice ON [tblCHDPStatus].[CHDPStatusID]=[tblPractice].[CHDPStatusID]) AND tblCity INNER JOIN tblPractice ON [tblCity].[CityID]=[tblPractice].[CityPM177ID] WHERE ((Not ([tblPractice].[LegalName]) Is Null)) ORDER BY [tblPractice].[LegalName], [tblPractice].[CityPM177ID], [tblCity].[City];

Thanks,
Loralee

 
Old February 14th, 2005, 11:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Loralee,

I have no idea who invented INNER JOIN syntax, but I really wish they hadn't.

This is SO much clearer:

SELECT p.LegalName, ch.[CHDP Status], c.City, p.PracticeNum
FROM tblCHDPStatus ch, tblPractice p, tblCity c
WHERE ch.CHDPStatusID=p.CHDPStatusID
AND c.CityID=p.CityPM177ID
AND p.LegalName IS NOT NULL
ORDER BY p.LegalName, p.CityPM177ID, c.City

I havn't tested it, so I'm not sure it gives you what you want, but ALL "inner join" syntax SQL statements can be written so much more easily by using WHERE clauses. Also, table aliases really simplify your script, and avoid field names with spaces at any cost.

If the above doesn't work, let me know an I'll tweak it.

HTH,

Bob

 
Old February 16th, 2005, 12:47 AM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks, Bob. That runs perfectly.

Using the "WHERE" and aliasing does make it much easier to read. (Was a little surprized to see the space.....it wasn't supposed to be there). I've got several more I can practice on, and some code to clean up, too!:D

Thanks again,
Loralee





Similar Threads
Thread Thread Starter Forum Replies Last Post
Array as Source for List Box Coby Excel VBA 2 June 5th, 2013 05:07 PM
multi-column list box values moved to 2nd list box sbmvr Access VBA 1 May 14th, 2007 01:58 PM
select box/List box alphabetic sort sasidhar79 Javascript How-To 3 November 10th, 2004 03:04 AM
Populate List Box by Combo Box Selection mmcdonal Access 2 June 15th, 2004 12:08 PM
Search using drop down list box and a text box tcasp Classic ASP Basics 1 July 31st, 2003 02:58 PM





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