Return single value from multiple criteria

T

TRO

Here is a query that I am having trouble with. The query selects the correct
data, but what I need is to only show the ZIP that matches in the second
table.
For example, if the [mh zip] is the field that matches in the [dealer sales
zips] table, then it should show in a field called [ZIP], but if it is the
[O1 ZIP] that matches, then it should show in the [ZIP] field.




SELECT [Vehicle Registrations].[Activity Date], [Vehicle
Registrations].[Transaction ID], [Vehicle Registrations].VIN, [Vehicle
Registrations].MMID, [Vehicle Registrations].[O1 ZIP], [Vehicle
Registrations].[O2 ZIP], [Vehicle Registrations].[MH ZIP], [Vehicle
Registrations].[R1 ZIP], [Vehicle Registrations].[R2 ZIP]
FROM [Vehicle Registrations]
WHERE ((([Vehicle Registrations].[O1 ZIP]) In (select zip from [dealer sales
zips] where [dealer license number] = forms![dealer report
selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[O2 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[MH ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[R1 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[R2 ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true)));
 
M

Michel Walsh

Hi,


What an ugly WHERE clause.
WHERE ((([Vehicle Registrations].[O1 ZIP]) In (select zip from [dealer
sales
zips] where [dealer license number] = forms![dealer report
selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[O2 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[MH ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[R1 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[R2 ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true)));
Click to expand...


I would try

===============
SELECT DISTINCT vr.[Activity Date],
vr.[Transaction ID],
vr.VIN,
vr.MMID,
vr.[O1 ZIP],
vr.[O2 ZIP],
vr.[MH ZIP],
vr.[R1 ZIP],
vr.[R2 ZIP]

FROM [Vehicle Registrations] As vr
INNER JOIN
[dealer sales zips] AS dsz
ON dsz.zip IN( vr.[o1 zip], vr.[o2 zip], vr.[mh zip], vr.[r1 zip],
vr.[r2 zip])

WHERE
dsz.[dealer license number] = FORMA![dealer report selection]![dealer
number]
AND dsz.use = true
==============

and if you want just the matching zip, then:

==============
SELECT DISTINCT vr.[Activity Date],
vr.[Transaction ID],
vr.VIN,
vr.MMID,
dsz.zip

FROM [Vehicle Registrations] As vr
INNER JOIN
[dealer sales zips] AS dsz
ON dsz.zip IN( vr.[o1 zip], vr.[o2 zip], vr.[mh zip], vr.[r1 zip],
vr.[r2 zip])

WHERE
dsz.[dealer license number] = FORMA![dealer report selection]![dealer
number]
AND dsz.use = true
================


Note that most of your initial WHERE clause was about
(some dsz.zip)=vr.[o1 zip] OR (some dsz.zip)=vr.[o2 zip] OR ....
which is similar to an IN( ). It was just a matter to bring it back in the
FROM clause, and, gratis, the second table matching zip was available, for
free. The only problem we have bringing it into the FROM clause is that if
there is more than a single match, say dsz.zip=[o1 zip] and also =[o2 zip],
the INNER JOIN will bring us TWO records (one per match). To avoid that, we
need the DISTINCT, right after the SELECT.


Another nicer alternative would be to redesign your table [Vehicle
Registrations], create another table if required, with all those zip-fields
like:


someID, zip, zipReason ' fields
car1, aabbb, o1 zip
car1, ccddd, o2 zip
car1, ccaaa, mh zip ' data sample



I agree, this design is harder for direct human consultation (but human
interface should be through FORM, not through a TABLE), but it is much
easier to work with. Here, no more "IN( )", just a simple = on the zip
field!

And if you need that data re-arrange for human consultation, you can always
"CROSSTAB" it, and then, present the data into a FORM. That way you have
your work easy, through nice table design, and human consultation easy,
through form design. With your actual design, only half of the job is easy
and the other half, well, that is you, not me, that has to work with it, so
feel free to do as it pleases you :)


Hoping it may help,
Vanderghast, Access MVP




TRO said:
Here is a query that I am having trouble with. The query selects the
correct
data, but what I need is to only show the ZIP that matches in the second
table.
For example, if the [mh zip] is the field that matches in the [dealer
sales
zips] table, then it should show in a field called [ZIP], but if it is the
[O1 ZIP] that matches, then it should show in the [ZIP] field.




SELECT [Vehicle Registrations].[Activity Date], [Vehicle
Registrations].[Transaction ID], [Vehicle Registrations].VIN, [Vehicle
Registrations].MMID, [Vehicle Registrations].[O1 ZIP], [Vehicle
Registrations].[O2 ZIP], [Vehicle Registrations].[MH ZIP], [Vehicle
Registrations].[R1 ZIP], [Vehicle Registrations].[R2 ZIP]
FROM [Vehicle Registrations]
WHERE ((([Vehicle Registrations].[O1 ZIP]) In (select zip from [dealer
sales
zips] where [dealer license number] = forms![dealer report
selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[O2 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[MH ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[R1 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[R2 ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true)));
Click to expand...
 
T

TRO

Thank you for the reply. While it did not give me exactly what I needed, it
put me on the right track.
I found that the queries that you suggested were just to slow for my
application (50,000 records, each of 1400 dealers related to about 1/2 of
them makes for about 35 Million zip code checks). Yes I know that I am
pushing Access about to it's limit, I am going to move it over to SQL Server
for the production run (with a 200x more data).

I used a variation on the idea of adding another table for the zips and
using it to query.

Timothy

Michel Walsh said:
Hi,


What an ugly WHERE clause.
WHERE ((([Vehicle Registrations].[O1 ZIP]) In (select zip from [dealer
sales
zips] where [dealer license number] = forms![dealer report
selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[O2 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[MH ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[R1 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[R2 ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true)));
Click to expand...


I would try

===============
SELECT DISTINCT vr.[Activity Date],
vr.[Transaction ID],
vr.VIN,
vr.MMID,
vr.[O1 ZIP],
vr.[O2 ZIP],
vr.[MH ZIP],
vr.[R1 ZIP],
vr.[R2 ZIP]

FROM [Vehicle Registrations] As vr
INNER JOIN
[dealer sales zips] AS dsz
ON dsz.zip IN( vr.[o1 zip], vr.[o2 zip], vr.[mh zip], vr.[r1 zip],
vr.[r2 zip])

WHERE
dsz.[dealer license number] = FORMA![dealer report selection]![dealer
number]
AND dsz.use = true
==============

and if you want just the matching zip, then:

==============
SELECT DISTINCT vr.[Activity Date],
vr.[Transaction ID],
vr.VIN,
vr.MMID,
dsz.zip

FROM [Vehicle Registrations] As vr
INNER JOIN
[dealer sales zips] AS dsz
ON dsz.zip IN( vr.[o1 zip], vr.[o2 zip], vr.[mh zip], vr.[r1 zip],
vr.[r2 zip])

WHERE
dsz.[dealer license number] = FORMA![dealer report selection]![dealer
number]
AND dsz.use = true
================


Note that most of your initial WHERE clause was about
(some dsz.zip)=vr.[o1 zip] OR (some dsz.zip)=vr.[o2 zip] OR ....
which is similar to an IN( ). It was just a matter to bring it back in the
FROM clause, and, gratis, the second table matching zip was available, for
free. The only problem we have bringing it into the FROM clause is that if
there is more than a single match, say dsz.zip=[o1 zip] and also =[o2 zip],
the INNER JOIN will bring us TWO records (one per match). To avoid that, we
need the DISTINCT, right after the SELECT.


Another nicer alternative would be to redesign your table [Vehicle
Registrations], create another table if required, with all those zip-fields
like:


someID, zip, zipReason ' fields
car1, aabbb, o1 zip
car1, ccddd, o2 zip
car1, ccaaa, mh zip ' data sample



I agree, this design is harder for direct human consultation (but human
interface should be through FORM, not through a TABLE), but it is much
easier to work with. Here, no more "IN( )", just a simple = on the zip
field!

And if you need that data re-arrange for human consultation, you can always
"CROSSTAB" it, and then, present the data into a FORM. That way you have
your work easy, through nice table design, and human consultation easy,
through form design. With your actual design, only half of the job is easy
and the other half, well, that is you, not me, that has to work with it, so
feel free to do as it pleases you :)


Hoping it may help,
Vanderghast, Access MVP




TRO said:
Here is a query that I am having trouble with. The query selects the
correct
data, but what I need is to only show the ZIP that matches in the second
table.
For example, if the [mh zip] is the field that matches in the [dealer
sales
zips] table, then it should show in a field called [ZIP], but if it is the
[O1 ZIP] that matches, then it should show in the [ZIP] field.




SELECT [Vehicle Registrations].[Activity Date], [Vehicle
Registrations].[Transaction ID], [Vehicle Registrations].VIN, [Vehicle
Registrations].MMID, [Vehicle Registrations].[O1 ZIP], [Vehicle
Registrations].[O2 ZIP], [Vehicle Registrations].[MH ZIP], [Vehicle
Registrations].[R1 ZIP], [Vehicle Registrations].[R2 ZIP]
FROM [Vehicle Registrations]
WHERE ((([Vehicle Registrations].[O1 ZIP]) In (select zip from [dealer
sales
zips] where [dealer license number] = forms![dealer report
selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[O2 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[MH ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true))) OR ((([Vehicle
Registrations].[R1 ZIP]) In (select zip from [dealer sales zips] where
[dealer license number] = forms![dealer report selection]![dealer number]
and [use] = true))) OR ((([Vehicle Registrations].[R2 ZIP]) In (select zip
from [dealer sales zips] where [dealer license number] = forms![dealer
report selection]![dealer number] and [use] = true)));
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


A possibly faster solution can be to make a query like:


SELECT DISTINCT zip FROM [dealer sales zips]
WHERE [dealer license number] = FORMS![dealer report selection]![dealer
number] AND use = true



then, use:

SELECT DISTINCT vr.[Activity Date],
vr.[Transaction ID],
vr.VIN,
vr.MMID,
vr.[O1 ZIP],
vr.[O2 ZIP],
vr.[MH ZIP],
vr.[R1 ZIP],
vr.[R2 ZIP]

FROM [Vehicle Registrations] As vr
INNER JOIN
savedQuery AS dsz
ON dsz.zip IN( vr.[o1 zip], vr.[o2 zip], vr.[mh zip], vr.[r1 zip],
vr.[r2 zip])



Having done the WHERE clause BEFORE the join, that could accelerate the
execution of the overall query, MAINLY if [dealer license number] =
FORMS![dealer report selection]![dealer number] is a condition that removes
a lot of records from further considerations.


Vanderghast, Access MVP
 

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

Update Query with multiple criteria 10
Adding field from seperate tables in a Report 1
NOT criteria in query not working 5
How would you select multiple locations with a specified distance 1
Query with Zip Code Mis-match (5 digit vs 9 digit) 2
Passing Criteria From a Form 1
Parameter query with udefined numer of parameters 1
UNUION ALL questions 8

Top