A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Tables with Lookup queries



 
 
Thread Tools Display Modes
  #1  
Old February 20th, 2007, 02:07 PM posted to microsoft.public.access.queries
JoHickey
external usenet poster
 
Posts: 7
Default Tables with Lookup queries

I have a library table. When entering the data for the County, I want it to
lookup from the County table and have a list to choose from. That works just
fine.

The next field is Municipality. I have a Municipality table for lookup, but
I only want to show the Municipalities that come from the County just chosen.

This is what I have, but it doesn't work.

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities RIGHT JOIN Counties ON Municipalities.CTYCode =
Counties.CTYCode
WHERE (((Municipalities.CTYCode)=[Counties]![CTYCode]));

Any help would be greatly appreciated.

Thanks

  #2  
Old February 20th, 2007, 03:01 PM posted to microsoft.public.access.queries
Ron2006
external usenet poster
 
Posts: 936
Default Tables with Lookup queries


I believe you can simplify it significantly. Try this:

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities
WHERE Municipalities.CTYCode = forms![thenameoftheformyouare on]![thenameofyourcitycodefield];


There is really no need for the join to the city table for the
condition is simply that the municipalities are part of the city
chosen in your prior combo.

Ron

  #3  
Old February 20th, 2007, 03:33 PM posted to microsoft.public.access.queries
Ron2006
external usenet poster
 
Posts: 936
Default Tables with Lookup queries

On Feb 20, 7:01 am, "Ron2006" wrote:
I believe you can simplify it significantly. Try this:

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities
WHERE Municipalities.CTYCode = forms![thenameoftheformyouare on]![thenameofyourcitycodefield];


There is really no need for the join to the city table for the
condition is simply that the municipalities are part of the city
chosen in your prior combo.

Ron


looking at it again, I am not sure I got the names correct on the
WHERE clause.

Basicaly, you want to limit the query of municipalities to only those
that have a county that matches the combo box selection. No other
tables/queries need be involved.

Ron

Ron

  #4  
Old February 20th, 2007, 03:47 PM posted to microsoft.public.access.queries
JoHickey
external usenet poster
 
Posts: 7
Default Tables with Lookup queries

Thank you Ron....that worked perfectly!

"Ron2006" wrote:


I believe you can simplify it significantly. Try this:

SELECT Municipalities.Municipality, Municipalities.MUNCode
FROM Municipalities
WHERE Municipalities.CTYCode = forms![thenameoftheformyouare on]![thenameofyourcitycodefield];


There is really no need for the join to the city table for the
condition is simply that the municipalities are part of the city
chosen in your prior combo.

Ron


 




Thread Tools
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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.