How to write SQl query for Access forms

[amitbadgi]

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 have wriiten a query and added it as a
macro to the 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 to write the query so that I can include all the
cities in this form ,and so that if a user enters a business id say
from city A, he would get teh details of that ID from city A and
exclude other cities, and say if he enters business ID from city B, he
would get the details from B, and exclude the rest. Thanks in advance.

 

[Guest]

I don't get what you are looking for. How about some sample data?

how about this? Have a main form with the following SQL:

SELECT *
FROM dbo_businessNC

When the user click on the Search button, display the detail in the
subform. The SQL for the subform is:

SELECT *
FROM dbo_licenseNC
WHERE bus_id = [Forms]![Form1].[text2]

 

[amitbadgi]

Hello Nospam.com, thankx for your reply, I will explain u exactly what
I am looking for,
There are 4 different cities and each city has 2 access tables from
which I need to pull my data, I had written a query which selects teh
required data from one city, now each city has different business ID
and different data to pull out, like for example, when a user enters a
BID 16139, say it belongs to city A, hence it shld pull in the adress,
license num, phonenum from teh table(thats in city A), and then if he
enters say 17890, which belongs to city B, it shld pull out adress,
license num, phonenum from city B. Now my question is that, I have
written a query which works for one city with 2 tables, I want to know
how do I combine all teh cities so that when a user enters a BID he
would get the required data from the related table and not include
anyother data from any other city. I hope this clears your doubt, here
is the query that i had written

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 add in this query too,
SELECT [Hawthorne Acc summary].[Account Name]
FROM [Hawthorne Acc summary]
WHERE (([Hawthorne Acc summary].[ID]=[Form].[Form1].[Text2]));
UNION SELECT [Martinez Business License Master]. [ID] FROM [Martinez
Business License Master] WHERE [Martinez Business License Master].[ID]
= [Form].[Form1].[Text2];

So I am not sure how to combine both these queries to get teh right
data to get selected.

Thankx in advance.

I don't get what you are looking for. How about some sample data?

how about this? Have a main form with the following SQL:

SELECT *
FROM dbo_businessNC

When the user click on the Search button, display the detail in the
subform. The SQL for the subform is:

SELECT *
FROM dbo_licenseNC
WHERE bus_id = [Forms]![Form1].[text2]

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 have wriiten a query and added it as a
macro to the 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 to write the query so that I can include all the
cities in this form ,and so that if a user enters a business id say
from city A, he would get teh details of that ID from city A and
exclude other cities, and say if he enters business ID from city B, he
would get the details from B, and exclude the rest. Thanks in advance.