creating forms in access

[bob123]

Hi guys, I am cuurently usind MS Access to create a form where in you enter
a business id and click search and teh corresponding details of that
Business Id shows up, I wriiten a query and added it as a macro to teh
search button. Now my problem is that, I have 4 different cities and each
city has 2 tables, now this is the query i had written for one city which
consists of 2 tables

SELECT dbo_businessNC.bus_name, dbo_businessNC.adrs1, dbo_businessNC.adrs2,
dbo_businessNC.city, dbo_businessNC.state, dbo_businessNC.zip,
dbo_businessNC.phone, dbo_licenseNC.license
FROM dbo_businessNC, dbo_licenseNC
WHERE (((dbo_businessNC.bus_id)=[Forms].[Form1].[text2] And
(dbo_businessNC.bus_id)=[dbo_licenseNC].[bus_id]));

Now I want to know how do write teh query so that I can include all teh
cities in this form ,a nd so that if a user enters a business id from any
city he would get the details accordingly. Thanks in advance.

 

[John Vinson]

Hi guys, I am cuurently usind MS Access to create a form where in you enter
a business id and click search and teh corresponding details of that
Business Id shows up, I wriiten a query and added it as a macro to teh
search button. Now my problem is that, I have 4 different cities and each
city has 2 tables, now this is the query i had written for one city which
consists of 2 tables

Well... storing data (the city) in a table name is Bad Design, I fear.
SQL/Server is QUITE capable of handling all four cities' worth of data
in one table; you can use Views (in SQL) or Access Queries (in Access)
to pull out the data for one city. What will you do if you need to add
a fifth city, or a sixth? Redesign your database?

SELECT dbo_businessNC.bus_name, dbo_businessNC.adrs1, dbo_businessNC.adrs2,
dbo_businessNC.city, dbo_businessNC.state, dbo_businessNC.zip,
dbo_businessNC.phone, dbo_licenseNC.license
FROM dbo_businessNC, dbo_licenseNC
WHERE (((dbo_businessNC.bus_id)=[Forms].[Form1].[text2] And
(dbo_businessNC.bus_id)=[dbo_licenseNC].[bus_id]));

Now I want to know how do write teh query so that I can include all teh
cities in this form ,a nd so that if a user enters a business id from any
city he would get the details accordingly. Thanks in advance.

Since this will presumably be read-only, create a UNION query and base
the form upon it. For best performance I'd suggest creating it as a
SQL View and linking to the view.

John W. Vinson[MVP]