Sames Items with Diff Dates

[dstevens]

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

 

[Gerald Stanley]

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

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.

 

[dstevens]

thanks a million... with a little playing around with it, I got it to work exactly as you mentioned. it looked so simple too