• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Ms Access - Update Query

Hi,

I need help with an update query that I just can not get my head around right now.

I get data from our HR system to populate the table tblOpLogQry The data may contain the same employee multiple times of when they have been overseas.

I need to update the table/fields (tblPersDetailsOPR) with the data from tblOpLogQry using only the max date row where tblOpLogQry!ID = tblPersDetailsOPR!EID.

Fields to be updated:
tblPersDetailsOPR!OPR-LastDeployRTUDate UPDATE TO tblOpLogQry!RTADate
tblPersDetailsOPR!OPR-LastDeployRTU UPDATE TO tblOpStructure!OPCode

The second field above (OpCode), I would prefer this to be updated with the value from tblOpStructure!OpName where tblOpLogQry![Operation Code] = tblOpStructure!OpCode.

I hope all this makes sense. I have attached an example mdb with some fake data.

Thanks in advance to anyone who may help.

Kev









ExpertsExchange.mdb
0
Kev
Asked:
Kev
  • 5
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you want to learn how to write such updates, please read this article:
http://www.experts-exchange.com/A_1517.html

the syntax for ms access is a bit down in the user comments
happy coding
0
 
GRayLCommented:
Does this do it?

UPDATE tblPersDetails a
INNER JOIN tblOpLogQry b ON a.EID = b.ID
INNER JOIN tblOpStructure c ON b.[Operation Code] = c.OpCode SET
a.OPR-LastDeployRTUDate = b.RTADate,
a.OPR-LastDeployRTU = b.OPCode

0
 
KevAuthor Commented:
Hi Grayl,

Getting error, "Syntax error (missing operator) in query expression....."

Kev
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
GRayLCommented:
Could you post a screen shot of the error message window?  

You can see if the JOINs work by replacing 'UPDATE' with 'SELECT * FROM'.  In addition, as the field names contain a 'strange' character (-), it's probably wise to wrap them both in square brackets.

SELECT * FROM tblPersDetails a
INNER JOIN tblOpLogQry b ON a.EID = b.ID
INNER JOIN tblOpStructure c ON b.[Operation Code] = c.OpCode SET
a.[OPR-LastDeployRTUDate] = b.RTADate,
a.[OPR-LastDeployRTU] = b.OPCode;

0
 
KevAuthor Commented:
Hi Grayl,

Error code attached.

Kev.
Error.png
0
 
GRayLCommented:
Lets start over:

UPDATE tblPersDetails a
INNER JOIN tblOpLogQry b
(INNER JOIN tblOpStructure c ON b.[Operation Code] = c.OpCode)
 ON a.EID = b.ID SET
a.OPR-LastDeployRTUDate = b.RTADate,
a.OPR-LastDeployRTU = b.OPCode;


0
 
KevAuthor Commented:
Still getting another error, this time "Syntax error in update statement".

Have you tried the code in the example I uploaded ? If so, did it work ? I want to rule out me doing something wrong.

Kev
0
 
GRayLCommented:
I had to back to the question and after a very thorough re-read, and 4 hours of testing I came up with this.  Although you have 96 records in the original table, only 90 will update because of mismatches/nulls:

UPDATE tblPersDetailsOPR e, (SELECT a.ID, c.OPName, a.RTADate
FROM tblOpLogQry AS a INNER JOIN tblOpStructure AS c ON a.[Operation Code] = c.OPCode
WHERE (((a.RTADate)=(SELECT Max(b.RTADate) FROM tblOpLogQry b WHERE b.ID = a.ID)))
ORDER BY a.ID) d SET
e.[OPR-LastDeployRTU] = d.OPName,
e.[OPR-LastDeployRTUDate] = d.RTADate
WHERE e.LinkEID = d.ID;
0
 
KevAuthor Commented:
Wow, thank you very much.
Yep, there will not always be matches.
I tested it in my DB and it worked perfectly.
Thanks a lot for your continued assistance, I think I just learned a lot about update queries with subqueries.

Kev
0
 
KevAuthor Commented:
Thank you so much for an outstanding solution and a lot of work on your part.
0
 
GRayLCommented:
Thanks, glad to help,

From an old Fighter Pilot
0
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now