Design Query to Update a Field

[SanAntonioSpurFan]

I have a table called RawData that contains the following fields and data looks like this:
UPC, Item Code, Last Scan Date
500,001,4/19/2004
501,001,3/2/2002
200,0,4/18/2004
300,0,4/18/2004

I want to build a update query that will look for duplicates on Item Code if Item Code is greater than zero. If it finds any, I want the query to look at Last Scan Date and pick the Max value and then update the field UPC with the correct UPC or highest Last Scan Date UPC. Need to ignore if Item Code equals Zero since the table will contain many zero with different upc's.

So the results should update the field UPC for the second record about from 501 to 500 and that's it. Nothing else..

 

[Golom]

Try
[blue][tt]
UPDATE tbl T SET

T.UPC =

(Select UPC From tbl X
Where X.LastScanDate =

(Select MAX(LastScanDate) From tbl Y
Where Y.UPC = T.UPC
AND Y.ItemCode <> 0))

From tbl T

Where T.ItemCode <> 0
[/tt][/blue]
This will update some UPC codes to themselves (e.g. "200" and "300" will be updated to the same values.)

 

[SanAntonioSpurFan]

Golom,

My table that contains this data is called RawData. I don't see a reference to it. Is it tbl X or Y or T? Or do I not even need to mention it?

thanks!

 

[lespaul]

Everywhere that you see 'tbl' replace it with your tablename.

Leslie

 

[SanAntonioSpurFan]

I get a syntax error(missing operator) in query expresion. The cursor blinks on the From Tbl T statement.

I replaced tbl with RawData....Any clue?

 

[lespaul]

Post the query that you're trying to run.

Leslie

 

[SanAntonioSpurFan]

Here it is...
UPDATE RawData T SET

T.UPC =

(Select UPC From RawData X
Where X.LastScanDate =

(Select MAX(LastScanDate) From RawData Y
Where Y.UPC = T.UPC
AND Y.ItemCode <> 0))

From RawData T

Where T.ItemCode <> 0

 

[Golom]

Sorry ... my mistake. Remove the "From" clause.

 

[SanAntonioSpurFan]

OK. Now it says "Operation must use a updateable query." Here's the new query....

UPDATE RawData T SET

T.UPC =

(Select UPC From RawData X
Where X.LastScanDate =

(Select MAX(LastScanDate) From RawData Y
Where Y.UPC = T.UPC
AND Y.ItemCode <> 0))

 

[lespaul]

Don't know if this will fix your issue, but you still need the where portion:

UPDATE RawData T SET

T.UPC =

(Select UPC From RawData X
Where X.LastScanDate =

(Select MAX(LastScanDate) From RawData Y
Where Y.UPC = T.UPC
AND Y.ItemCode <> 0))

Where T.ItemCode <> 0

Leslie

 

[SanAntonioSpurFan]

I put it back in and get the same message "Operation must use a updateable query." Query now looks like this...

UPDATE RawData T SET

T.UPC =

(Select UPC From RawData X
Where X.LastScanDate =

(Select MAX(LastScanDate) From RawData Y
Where Y.UPC = T.UPC
AND Y.ItemCode <> 0))

Where T.ItemCode <> 0