Sames Items with Diff Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have quite a large data file that looks like this

inv# voucher id dat
556677 12345678 4/6/200
556677 12345678 4/7/200
556677 12345678 4/8/200

There are approximately 60,000 records with two or more fields with dup inv# &voucher ID.

I'd really appreciate if someone could help me out with a routine that will pick out the dup with the most current date
In the above example, the result that I'm looking for would be the entry with the 4/8/2004 date. There are other examples where there are eight invoice numbers witht he same voucher but all have different dates. Any suggestions or ideas would be really appreciated
Thanks
 
Try something like

SELECT inv#, voucherId, Max([date]) AS latestDate
FROM {YourTableName}
GROUP BY inv#, voucherId
HAVING Count([date])>1;

If you also want those entries where there are no duplicate
inv#, vouucherId combinations, you should omit the Having
clause.

Hope This Helps
Gerald STanley MCSD
-----Original Message-----
Hi
I have quite a large data file that looks like this:

inv# voucher id date
556677 12345678 4/6/2004
556677 12345678 4/7/2004
556677 12345678 4/8/2004

There are approximately 60,000 records with two or more
Click to expand...
fields with dup inv# &voucher ID.
I'd really appreciate if someone could help me out with a
Click to expand...
routine that will pick out the dup with the most current date.
In the above example, the result that I'm looking for
Click to expand...
would be the entry with the 4/8/2004 date. There are other
examples where there are eight invoice numbers witht he
same voucher but all have different dates. Any suggestions
or ideas would be really appreciated.
 
thanks a million... with a little playing around with it, I got it to work exactly as you mentioned. it looked so simple too :-)
 
Back
Top