Proper Query Setup 1

[SanAntonioSpurFan]

I have a table called RawData that contains 2.5M records. The table has 3 fields that I need to use. Field 1 = UPC, Field 2 = Item Code, Field 3 = Last Scan Date. Some of the fields contain old UPC codes that I want to replace with the correct UPC codes.

I want to construct a query that will look at the field Item Code and if it is greater than zero, I have some zero's in this field that I want to ignore, look for any duplicte item code records, if you find a match then look at Last Scan Date and pick the UPC that has the Highest Date Value.

Example:
Item Code, UPC, Last Scan Date
555, 2004, 4/15/2004
555, 0034, 3/15/2004

I want the replace the 0034 with 2004 since this code has the more recent Last Scan Date.

One more thing - The table also contains Weeks, and Stores so there will be many duplicate item codes. Maybe a grouping needs to happen first that isolates the problem children in a make table query then perform the action then link on item code and replace upc? or something like that? I can't forget about the zero values in Item Code because they all have different upc codes that I can't loose. Any help would be great!

 

[ByteMyzer]

Try this query:

SELECT R1.*
FROM RawData AS R1
WHERE R1.[Last Scan Date]=
(SELECT Max(R2.[Last Scan Date])
FROM RawData AS R2
WHERE R2.[Item Code]=R1.[Item Code]);