Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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
0
shieldsco
Asked:
shieldsco
  • 4
  • 3
  • 2
1 Solution
 
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
 
shieldscoAuthor Commented:
I have added PK's to the tables - what's next
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
aikimarkCommented:
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
 
shieldscoAuthor Commented:
Thanks -- Works Good
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now