## 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
Solved

# Use Update Query to Update First Occurence

Posted on 2013-06-25
268 Views
I'm trying to use an update query to update the first occurence of the IDDCash field in the tblCumm from the [tblSumIDDTB]![IDDBalance].  Any Thoughts

UPDATE (tblAppropriation INNER JOIN tblCumm ON tblAppropriation.SF224Appropriation = tblCumm.SF224Appropriation) INNER JOIN tblSumIDDTB ON tblAppropriation.TBAppropration = tblSumIDDTB.Appropriation SET tblCumm.IDDCash = [tblSumIDDTB]![IDDBalance];
Update.xlsx
0
Question by:shieldsco
• 4
• 3
• 2

LVL 45

Expert Comment

ID: 39277993
not without an autonumber column in the tables.
0

LVL 100

Expert Comment

ID: 39278036
Or some other method of determining the FIRST record such as a date field where the minimum or maximum date is the first record

mlmcc
0

Author Comment

ID: 39278358
I have added PK's to the tables - what's next
0

LVL 100

Expert Comment

ID: 39278375
How can you use the PK to determine the record to update?

mlmcc
0

Author Comment

ID: 39278397
the first instance
0

LVL 100

Expert Comment

ID: 39278415
What does that mean when you look at the PKs?

Is it the minimum PK, maximum PK?
First is determined by the order the records are retrieved.

mlmcc
0

Author Comment

ID: 39278465
It is the min
0

LVL 45

Accepted Solution

aikimark earned 500 total points
ID: 39278511
Your PK is an autonumber column in the tblCumm table?

If so, you would add a condition in a WHERE clause that would limit the row being updated to the one with the MIN(ID) value for the set of rows.

Example:
``````UPDATE (tblAppropriation INNER JOIN tblCumm ON tblAppropriation.SF224Appropriation = tblCumm.SF224Appropriation) INNER JOIN tblSumIDDTB ON tblAppropriation.TBAppropration = tblSumIDDTB.Appropriation
SET tblCumm.IDDCash = [tblSumIDDTB]![IDDBalance]
WHERE tblCumm.ID = (Select Min(F.ID) From tblCumm As F Where F.SF224Appropriation =
tblCumm.SF224Appropriation)
``````
0

Author Closing Comment

ID: 39278600
Thanks -- Works Good
0

## Featured Post

Question has a verified solution.

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