[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Update Query - update same record multiple times

Posted on 2008-06-11
14
Medium Priority
?
409 Views
Last Modified: 2011-09-20
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.
0
Comment
Question by:dcorleto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21782152
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?
0
 

Author Comment

by:dcorleto
ID: 21782840
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.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21788911
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 44

Expert Comment

by:GRayL
ID: 21788916
I'm saying there must be a datetime or something similar associated with each record?
0
 

Author Comment

by:dcorleto
ID: 21793826
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.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 750 total points
ID: 21794634
To ensure that Jet selects the 'last' record, you need the datetime stamp.  Assuming the field is named DTStamp,

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);
0
 

Author Comment

by:dcorleto
ID: 21794795
I'm going to give that a try - will let you know - thank you so much.
0
 

Author Comment

by:dcorleto
ID: 21794938
do I not use the Update statement?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21795005
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.
0
 

Author Comment

by:dcorleto
ID: 21795026
So can I still run this as an Update Query from Access or do I have to use VBA?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21795129
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?
0
 

Author Comment

by:dcorleto
ID: 21821141
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.
0
 

Author Comment

by:dcorleto
ID: 21822085
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)));
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21839327
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?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question