Use Update Query to Update First Occurence

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
shieldscoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aikimarkConnect With a Mentor Commented:
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)

Open in new window

0
 
aikimarkCommented:
not without an autonumber column in the tables.
0
 
mlmccCommented:
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
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'.

 
shieldscoAuthor Commented:
I have added PK's to the tables - what's next
0
 
mlmccCommented:
How can you use the PK to determine the record to update?

mlmcc
0
 
shieldscoAuthor Commented:
the first instance
0
 
mlmccCommented:
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
 
shieldscoAuthor Commented:
It is the min
0
 
shieldscoAuthor Commented:
Thanks -- Works Good
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.

All Courses

From novice to tech pro — start learning today.