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?
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.