Query using max

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

We use Access 2000. I need to write a query or queries
to do the following:

Find duplicate records in a table where the Serial#
matches. The fields in the table are:
Location, Serial#, ScanDate, ScanTime

For any duplicates Serial#s, I need to insert the record
with the most recent ScanDate and ScanTime into another
table. The other duplicates can be thrown out.

I have created a series of queries using Group By and Max
and am able to get the duplicate Serial# records into a
table but I can't get only the ones with the max date.

My query uses Group By on the Serial# and Location
fields, and Max on the ScanDate and ScanTime fields. For
criterial under Serial# I have:

In (SELECT[Serial#] FROM [FormTxtStagingTable] As Tmp
GROUP BY [Serial#] HAVING Count(*)>1 )

I get only records where there are duplicate serial#'s
but not just the max records - I get all records. For
example, I get the following:

Location Serial# ScanDate ScanTime
41-115 210PQ31 20040303 1150
41-106 21OPQ31 20040303 1155
41-104 1GQJQ31 20040303 1151
41-105 1GQJQ31 20040303 1152

What I would like to get is:

Location Serial# ScanDate ScanTime
41-106 21OPQ31 20040303 1155
41-105 1GQJQ31 20040303 1152

I have tried making a second query linking the first
query described above and the table where the data is
stored so I can get the duplicate records and using the
max function on scandate and scantime. I get the same 4
records as above.

Is it possible to do what I want to do? Can anyone
help? Thanks in advance.
Mary
 
I think you want to make a query, group on serial number,
max on date, and max on time. Then, create a 2nd query,
join first query to your table. Join on serial number,
date, and time. Select all fields from 1st query and
select location from table.

HTH
 
Back
Top