SQL Query MAX JOIN UPDATE

Have two tables:  A and B
Table A = TimeStamp, TAG(KEY), Location, Tech, Model, Serial, EmployeeID
Table B = TimeStamp, Tag, Location

Need to UPDATE 3 fields in A based on on 3 Fields in B
Notes: TimeStamp in A will be the old TimeStamp, TimeStamp in B will the the newer TimeStamp, TAG in A is PRIMARY KEY, Tag in B is NOT Primary and may appear in several records with a different timestamp. Location will be various entries.

Example of Table B:
TAG      TimeStamp                           LOCATION
CC000417      5/20/2008 3:38:43 PM      A
CC000418      5/20/2008 3:47:02 PM      B
CC000418      5/22/2008 8:49:58 AM      A
CC000419      5/20/2008 3:49:25 PM      C
CC000419      5/28/2008 7:13:58 PM      A

After running SQL Query, 3 records in Table A should be updated as Follows:

CC000417      5/20/2008 3:38:43 PM      A
CC000418      5/22/2008 8:49:58 AM      A
CC000419      5/28/2008 7:13:58 PM      A

Here is just one attempt of about 50 that do not work - get an aggregate error.  Please help, perhaps this is not as easy as it would appear.
SELECT LINK.TAG, max(TimeStamp), LINK.LOCATION
FROM LINK
order  BY LINK.TAG, LINK.TimeStamp DESC;
dcorletoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MarcjevCommented:
Use something like this:
*determine the tag and max timestamp
*join this with the orginal table to only retrieve the records you need

(read timest  = timestamp...)
[remark: i tried this using ms sql 2005, but approach should also work in access though)

select tableb.tag, tableb.timest, tableb.location
from tableb
join (  select tag, max(timest) timest from tableb
            group by tag ) as maxtagst
on tableb.Tag = maxtagst.tag
and tableb.timest = maxtagst.timest
0
BadotzCommented:
>Max< is an aggregat function which requires you to group your rows. Try:

SELECT L.TAG, max(L.TimeStamp), L.LOCATION
FROM LINK AS L
GROUP BY L.TAG, L.LOCATION
ORDER  BY L.TAG, L.TimeStamp DESC;
0
dcorletoAuthor Commented:
Badotz:

Still aggregate error with that code useing Access 2003
Capture1.JPG
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

dcorletoAuthor Commented:
Can you please tell me what this means?  (read timest  = timestamp...)

and how I would or do I need it somewhere in the query?
0
BadotzCommented:
What kind of field is >TimeStamp< ??
0
dcorletoAuthor Commented:
TimeStamp is Date/Time
0
BadotzCommented:
This will select the 'A' rows, but it does not update table 'A':

SELECT b.TAG, b.TimeStamp, b.LOCATION
FROM LINK_B AS b INNER JOIN [SELECT tag, max(timestamp) AS MaxOfTimeStamp FROM LINK_B GROUP BY tag; ]. AS maxtagst ON (b.TAG = maxtagst.tag) AND (b.TimeStamp = maxtagst.MaxOfTimeStamp);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dcorletoAuthor Commented:
YES YES  - SO CLOSE!!
I updated the syntax - now just need to update that info into Table A?  Can it be done?

SELECT b.TAG, b.TimeStamp, b.LOCATION
FROM LINK AS b INNER JOIN [SELECT tag, max(timestamp) AS MaxOfTimeStamp FROM LINK GROUP BY tag; ]. AS maxtagst ON (b.TimeStamp = maxtagst.MaxOfTimeStamp) AND (b.TAG = maxtagst.tag);
0
BadotzCommented:
Sorry - Access mungs the SQL so it is both unrunnable and uneditable. For this reason, *never* veiw this query in the Query Designer - always view it in SQL View. In fact, keep the query handy by copying it into a .SQL file you keep in the same directory as your .MDB file:

SELECT b.tag, b.timestamp, b.location
FROM LINK_B AS b
INNER JOIN (select tag, max(timestamp) AS maxtimestamp from LINK_B group by tag) as maxtag
ON b.tag = maxtag.tag and b.timestamp = maxtag.maxtimestamp;
0
dcorletoAuthor Commented:
Seems ok in Access 2003 - I can view and run in design mode.  How would I then get the info to update Table A?
Capture2.JPG
0
dcorletoAuthor Commented:
I changed it to a Make Table Query and then create a macro to update from there - it seems to work - do I need to worry about corrupted data?
0
BadotzCommented:
>>do I need to worry about corrupted data?

Why would you think there is corrupted data? Access only mungs the SQL, *not* the data.

btw: "mung" is an ancient (well...) self-referential acronym for "mung until no good", which means "apply a method over and over until the results are meaningless or unrecognizable, or both". It usually occurs inadvertently due to bugs in your code.
0
dcorletoAuthor Commented:
Did you get the points?  Hope I did that right - thanks so much for your help  - this has drove me crazy for several weeks...with some tweaks I'll have it up and running..and thanks for the definition!!
0
BadotzCommented:
Yes, I got the points.

But since I cannibalized the SQL, shouldn't some of the points have been awarded to Marcjev, too?
0
dcorletoAuthor Commented:
didn't hear back about questions read timest  = timestamp...) in the meantime, yours worked.
0
BadotzCommented:
OK...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.