Return single value from multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TRO - 27 Apr 2006 05:14 GMT

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)));

Reply to this Message

Michel Walsh - 27 Apr 2006 11:32 GMT

Hi,

What an ugly WHERE clause.

> WHERE ((([Vehicle Registrations].[O1 ZIP]) In (select zip from [dealer
> sales
[quoted text clipped - 10 lines]
> from [dealer sales zips] where [dealer license number] = forms![dealer
> report selection]![dealer number] and [use] = true)));

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

> Here is a query that I am having trouble with. The query selects the
> correct
[quoted text clipped - 25 lines]
> from [dealer sales zips] where [dealer license number] = forms![dealer
> report selection]![dealer number] and [use] = true)));

Reply to this Message

TRO - 27 Apr 2006 23:07 GMT

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

> Hi,
>
[quoted text clipped - 123 lines]
> > from [dealer sales zips] where [dealer license number] = forms![dealer
> > report selection]![dealer number] and [use] = true)));

Reply to this Message

Michel Walsh - 28 Apr 2006 11:12 GMT

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

> Thank you for the reply. While it did not give me exactly what I needed,
> it
[quoted text clipped - 10 lines]
>
> Timothy

Reply to this Message