Query Help
-
I have a Access Form, with a series of three combo boxes. Box 1 - City Box 2 - State Box 3 - ZIP When I input the zip code, I want to populate the other two boxes based on the result of the ZIP code. I have a ZIP codee table, which has ZIP, CITY, and a second table that has State. How should I write the query in order to accomplish this? Thanks,
-
I have a Access Form, with a series of three combo boxes. Box 1 - City Box 2 - State Box 3 - ZIP When I input the zip code, I want to populate the other two boxes based on the result of the ZIP code. I have a ZIP codee table, which has ZIP, CITY, and a second table that has State. How should I write the query in order to accomplish this? Thanks,
You can obtain the City by doing a simple SELECT on your ZIP Code table, filtering on the ZIP. You haven't explained how your tables are related. I assume you have a City table with a foreign key to the State table. If so, write a query which joins the ZIP Code, City and State tables and fetches the corresponding State description.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
You can obtain the City by doing a simple SELECT on your ZIP Code table, filtering on the ZIP. You haven't explained how your tables are related. I assume you have a City table with a foreign key to the State table. If so, write a query which joins the ZIP Code, City and State tables and fetches the corresponding State description.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Hello Paul, and thank you for your response. I need to get better explaining my brainstorming! Ok, the Zipcode table has Zipcode, City, State Code fields. The State Table has State Code, State Abrieviation, and State Name. The two tables are related by the State Code field. I would think that if I enter the zip, then perform an after update event that queries the Zipcode table and populates the State field of the form. So, with the above in mind, would you think that this query would work? SELECT [ZIP Codes].[City], [States].[State Abbreviation] FROM [ZIP Codes], States WHERE [Zip Codes].[ZIP Code] = '44107' and [States].[State Code] = (SELECT [ZIP Codes].[State Code] FROM [ZIP Codes] WHERE [ZIP Codes].[ZIP Code] = '44107') ORDER BY [ZIP Codes].[ZIP Code]; Thanks,
-
Hello Paul, and thank you for your response. I need to get better explaining my brainstorming! Ok, the Zipcode table has Zipcode, City, State Code fields. The State Table has State Code, State Abrieviation, and State Name. The two tables are related by the State Code field. I would think that if I enter the zip, then perform an after update event that queries the Zipcode table and populates the State field of the form. So, with the above in mind, would you think that this query would work? SELECT [ZIP Codes].[City], [States].[State Abbreviation] FROM [ZIP Codes], States WHERE [Zip Codes].[ZIP Code] = '44107' and [States].[State Code] = (SELECT [ZIP Codes].[State Code] FROM [ZIP Codes] WHERE [ZIP Codes].[ZIP Code] = '44107') ORDER BY [ZIP Codes].[ZIP Code]; Thanks,
solutionsville wrote:
SELECT [ZIP Codes].[City], [States].[State Abbreviation] FROM [ZIP Codes], States WHERE [Zip Codes].[ZIP Code] = '44107' and [States].[State Code] = (SELECT [ZIP Codes].[State Code] FROM [ZIP Codes] WHERE [ZIP Codes].[ZIP Code] = '44107') ORDER BY [ZIP Codes].[ZIP Code];
Your query is overly complicated and can be simplified to:
SELECT [ZIP Codes].[City], [States].[State Abbreviation]
FROM [ZIP Codes]
INNER JOIN [States]
ON [ZIP Codes].[State Code] = [States].[State Code]
WHERE [ZIP Codes].[ZIP Code] = '44107'Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
solutionsville wrote:
SELECT [ZIP Codes].[City], [States].[State Abbreviation] FROM [ZIP Codes], States WHERE [Zip Codes].[ZIP Code] = '44107' and [States].[State Code] = (SELECT [ZIP Codes].[State Code] FROM [ZIP Codes] WHERE [ZIP Codes].[ZIP Code] = '44107') ORDER BY [ZIP Codes].[ZIP Code];
Your query is overly complicated and can be simplified to:
SELECT [ZIP Codes].[City], [States].[State Abbreviation]
FROM [ZIP Codes]
INNER JOIN [States]
ON [ZIP Codes].[State Code] = [States].[State Code]
WHERE [ZIP Codes].[ZIP Code] = '44107'Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Thanks Paul. I Appreciate it. Brian
-
I have a Access Form, with a series of three combo boxes. Box 1 - City Box 2 - State Box 3 - ZIP When I input the zip code, I want to populate the other two boxes based on the result of the ZIP code. I have a ZIP codee table, which has ZIP, CITY, and a second table that has State. How should I write the query in order to accomplish this? Thanks,
Here's a tip for you. Use the Access Query design to do you table links etc, then look at query as SQL Statement and there's the required code.
Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)