SQL or maybe a hardware issue for finding lowest Repair order date or mileage

[Keith K]

Subject: Re: SQL or maybe a hardware issue for finding lowest Repair
order date or mileage
From: ksquared <[email protected]>
Newsgroups: microsoft.public.access.queries

UPDATE 8/4/2010

OUCH! No wonder that is slow. You have a correlated subquery (runs
once for every record in the table) combined with a cartesian join.
That means if you have 10,000 records, the query will build 10,000 *
10,000 records (1,000,000,000 records) to work with and then for every
one of those 1 billion records it will run the subquery in the where
clause.

So I would rewrite that query. The spaces in your table name means
that Access will have to "stack" queries to handle the subquery in the
FROM clause.

Save this query as qFirstDate - it should return the first service
date for each VIN plus dealer combination
SELECT RPFVIN, RPRDLR, Min(B.RPWODT) as FirstDate
FROM [First Service Data table]
WHERE RPRDLR ="NY075"
GROUP BY RPFVIN, RPRDLR

Now build a query with your table and this saved query.
SELECT A.RPRDLR, A.RPSEQN, A.RPWODT
, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY
FROM [First Service Data table] AS A INNER JOIN qFirstDate as Q
ON A.RPFVIN = Q.RPFVIN
AND A.RPWODT = Q.FirstDate
AND A.RPRDLR = Q.RPRDLR

Thanks just got back from a trip so I need to read your information and
digest many thanks Keith

UPDATE 8/4/2010

I'm sure there is some etiqutte i'm missing but John Spencers suggestions
moved me down the road. The result set is about 1.2 million records

The last obstacle to overcome is that while the query returns the lowest
mileage and date for a vin number (Vehicle ID) it will pull multiple
records unique to different servicing dealers. Approximately 75,000 VINS
with multiple records.

So I can identify the VIN's by counting the records in the result set and
identifying any VIN with multiple records. I suppose I could write these
records to a separate table. I still need to find the lowest date and
mileage for the VIN and eliminate the other data for the VIN. of the
75,000 records the range is from 2 to 5 records for the affected VINS.

My preference is to identify VINS with multiple records, write to a
table, erase duplicated records so for the group in question I end up
with the first RO date and lowest mileage per VIN. Then append this set
to the master table. Any ideas??

 

[John Spencer]

If you need the lowest (earliest) repair date regardless of servicing dealer
then change the qFirstDate query - rop the RPRDlr from the query

SELECT RPFVIN, Min(B.RPWODT) as FirstDate
FROM [First Service Data table]
WHERE RPRDLR ="NY075" <<< Not sure if you need the where clause ???
GROUP BY RPFVIN

The second query then becomes
SELECT A.RPRDLR, A.RPSEQN, A.RPWODT
, A.RHWOML, A.RPFVIN, A.RHCTTA,
A.RHWTTA, A.RHITTA, A.RHCPAY, A.RHIPAY
FROM [First Service Data table] AS A INNER JOIN qFirstDate as Q
ON A.RPFVIN = Q.RPFVIN
AND A.RPWODT = Q.FirstDate

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

Keith K wrote:
SNIP