UNION Query

V

VJ

Hi,

I am working on a query and I was going to use UNION but I want to make sure
it only pulls all the criteria requested in my search. I am doing a Vendor
database and I want it to pull City, Restaurant not to just pull everything
that pops up for the city as an example. I have categories to search by as
well location. My code looks like this.

SELECT Master.[Name of Property], Master.[Address 1], Master.[Address 2],
Master.City, Master.State, Master.Zip, Master.[NY Area], Master.[Main Phone],
Master.Category1, Master.Category2, Master.[Contact Name], Master.Phone
FROM Master
WHERE ((Master.City)=[Forms]![Search 2]![City]))
UNION
SELECT Master.[Name of Property], Master.[Address 1], Master.[Address 2],
Master.City, Master.State, Master.Zip, Master.[NY Area], Master.[Main Phone],
Master.Category1, Master.Category2, Master.[Contact Name], Master.Phone
FROM Master
WHERE (((Master.City)=[Forms]![Search 2]![City]) AND
((Master.Category1)=[Forms]![Search 2]![Category1]))

Do I need to put it into a certain order so that I can get the search I am
looking for? Help please! Thanks in advance.
VJ
 
J

John Spencer

You need to apply all the criteria in each part of the union.

Your example makes no real sense to me.
The first query will pull all records in the specified city
The second query will pull all records in the specified city in a
specific category.

The Union will then combine the results and strip out any duplicates -
so you will end up with all records in the specified city.

The order of the queries in the union has no effect on what is returned.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
V

VJ

Ok. I have a form for the users to search the database with. In this form
they can put in city, state, pick a category and enter 4 other criteria.
Based on the category I am trying to run a query that will pull what they
want. Does that make more sense? I think I need to just take out one of the
criteria. Thanks

John Spencer said:
You need to apply all the criteria in each part of the union.

Your example makes no real sense to me.
The first query will pull all records in the specified city
The second query will pull all records in the specified city in a
specific category.

The Union will then combine the results and strip out any duplicates -
so you will end up with all records in the specified city.

The order of the queries in the union has no effect on what is returned.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

I am working on a query and I was going to use UNION but I want to make sure
it only pulls all the criteria requested in my search. I am doing a Vendor
database and I want it to pull City, Restaurant not to just pull everything
that pops up for the city as an example. I have categories to search by as
well location. My code looks like this.

SELECT Master.[Name of Property], Master.[Address 1], Master.[Address 2],
Master.City, Master.State, Master.Zip, Master.[NY Area], Master.[Main Phone],
Master.Category1, Master.Category2, Master.[Contact Name], Master.Phone
FROM Master
WHERE ((Master.City)=[Forms]![Search 2]![City]))
UNION
SELECT Master.[Name of Property], Master.[Address 1], Master.[Address 2],
Master.City, Master.State, Master.Zip, Master.[NY Area], Master.[Main Phone],
Master.Category1, Master.Category2, Master.[Contact Name], Master.Phone
FROM Master
WHERE (((Master.City)=[Forms]![Search 2]![City]) AND
((Master.Category1)=[Forms]![Search 2]![Category1]))

Do I need to put it into a certain order so that I can get the search I am
looking for? Help please! Thanks in advance.
VJ
Click to expand...
Click to expand...
 
J

John Spencer

Well, it appears to me that a UNION query is not the solution. You need
to set up one query and apply the criteria.

IF your fields are text fields and ALWAYS contain data then you can use
something like

W
SELECT Master.[Name of Property], Master.[Address 1]
, Master.[Address 2]
, Master.City, Master.State, Master.Zip, Master.[NY Area]
, Master.[Main Phone],
Master.Category1, Master.Category2
, Master.[Contact Name], Master.Phone
FROM Master
WHERE Master.City Like Nz([Forms]![Search 2]![City],"*")
AND Master.Category1= Nz([Forms]![Search]![Category1],"*")

or if Category1 is a number field or can ever be null, then

WHERE Master.City Like Nz([Forms]![Search 2]![City],"*")
AND (Master.Category1= [Forms]![Search]![Category1]
OR [Forms]![Search]![Category1] is Null)



=================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Ok. I have a form for the users to search the database with. In this form
they can put in city, state, pick a category and enter 4 other criteria.
Based on the category I am trying to run a query that will pull what they
want. Does that make more sense? I think I need to just take out one of the
criteria. Thanks

John Spencer said:
You need to apply all the criteria in each part of the union.

Your example makes no real sense to me.
The first query will pull all records in the specified city
The second query will pull all records in the specified city in a
specific category.

The Union will then combine the results and strip out any duplicates -
so you will end up with all records in the specified city.

The order of the queries in the union has no effect on what is returned.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

I am working on a query and I was going to use UNION but I want to make sure
it only pulls all the criteria requested in my search. I am doing a Vendor
database and I want it to pull City, Restaurant not to just pull everything
that pops up for the city as an example. I have categories to search by as
well location. My code looks like this.

SELECT Master.[Name of Property], Master.[Address 1], Master.[Address 2],
Master.City, Master.State, Master.Zip, Master.[NY Area], Master.[Main Phone],
Master.Category1, Master.Category2, Master.[Contact Name], Master.Phone
FROM Master
WHERE ((Master.City)=[Forms]![Search 2]![City]))
UNION
SELECT Master.[Name of Property], Master.[Address 1], Master.[Address 2],
Master.City, Master.State, Master.Zip, Master.[NY Area], Master.[Main Phone],
Master.Category1, Master.Category2, Master.[Contact Name], Master.Phone
FROM Master
WHERE (((Master.City)=[Forms]![Search 2]![City]) AND
((Master.Category1)=[Forms]![Search 2]![Category1]))

Do I need to put it into a certain order so that I can get the search I am
looking for? Help please! Thanks in advance.
VJ
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export to text - no decimals 2
Duplicates don't show up in my Union Query 2
Query to select individual records 4
Union query 5
Union Query - Select Distinct 2 fields 7
Resetting Subform LinkMasterfields in runtime to apply differentfilters (access 2007) 0
Query to find a range of data 8
adding another table to a union query 1

Top