asked on

Update Query - update same record multiple times

Have the following Update Query:
UPDATE LINK INNER JOIN FORM_ID_358989923 ON LINK.TAG = FORM_ID_358989923.TAG SET FORM_ID_358989923.LOCATION = LINK!LOCATION;

In the LINK table, the Tag may appear several times, and this table is in chronological order, of when assets are moved. TAG is the asset tag, and the link table might have multiple records for that Tag, if were moved several times. The Update query runs, but if the Tag is in more than one record, only the first instance gets updated. Need it to update each time, therefore the last time it appears in the Link Table should be what shows as updated in the FORM_ID table - please help.

GRayL

Then you don't need each and every instance and they will continue to get written over. Only the last remains, there, update just once on the Last. How do you determine last?

dcorleto

ASKER

Consider the following:
TAG LOCATION
TAG01 A
TAG02 B
TAG03 C
TAG01 A
TAG04 D
TAG01 C

I am looking for the Update query to show The final locations for Tags 1,2,3 and 4. When the update is complete, Tag01 should show location C. The records are scanned in with a handheld barcode reader as the equipoment is moved and stored in the Link table.

GRayL

Yes, but how do you know that Location C is the 'last' location? It could have just as easily moved from A to C to A.

GRayL

I'm saying there must be a datetime or something similar associated with each record?

dcorleto

ASKER

I could add a date/timestamp field easily, but for now, there is only the location and field and the records are entered in order of movement.

ASKER CERTIFIED SOLUTION

GRayL

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dcorleto

ASKER

I'm going to give that a try - will let you know - thank you so much.

dcorleto

ASKER

do I not use the Update statement?

GRayL

No, once you run the UPDATE, you then need to run the SELECT statement to locate the location with the 'last' - most recent - Max dtStamp.

Remember, Actions queries like SELECT INTO, INSERT INTO, UPDATE, etc. do not return record sets.

dcorleto

ASKER

So can I still run this as an Update Query from Access or do I have to use VBA?

GRayL

I'm supposing you already have the UPDATE query saved under a name something like qudTags. You need to now create a new query using the SQL I gave you above which you should save as something like qryGetLastLocation. Are you OK with that?

dcorleto

ASKER

Still not working

For your statement,

Select a.Tag, a.Location FROM Link a WHERE a.Location In (SELECT Max(b.DTStamp) FROM Link b WHERE b.Tag = a.Tag AND b.Location = a.Location);

can your tell me what "a." and "link a" mean? I must have the syntax wrong.

dcorleto

ASKER

Dear GRayL:
I think I'm getting there but now get a data type mismatch in criteria error. All the data types match in the two tables. Any suggestions?

SELECT a.TAG, a.LOCATION
FROM FORM_ID_398779041 AS a
WHERE (((a.LOCATION) In (SELECT Max(b.TimeStamp) FROM FORM_ID_398779041 b WHERE b.Tag = a.Tag AND b.Location = a.Location)));

GRayL

I was away the past three days. While you accepted a solution and I was awarded a B, I would have preferred to work out the answer. I see the 'dumb' mistake:

SELECT a.TAG, a.LOCATION
FROM FORM_ID_398779041 AS a
WHERE (((a.TimeStamp) In (SELECT Max(b.TimeStamp) FROM FORM_ID_398779041 b WHERE b.Tag = a.Tag AND b.Location = a.Location)));

That any better?