Solved

Use Update Query to Update First Occurence

Posted on 2013-06-25
9
237 Views
Last Modified: 2013-06-26
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
Comment
Question by:shieldsco
  • 4
  • 3
  • 2
9 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 39277993
not without an autonumber column in the tables.
0
 
LVL 100

Expert Comment

by:mlmcc
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

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

Expert Comment

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

mlmcc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:shieldsco
ID: 39278397
the first instance
0
 
LVL 100

Expert Comment

by:mlmcc
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

by:shieldsco
ID: 39278465
It is the min
0
 
LVL 45

Accepted Solution

by:
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)

Open in new window

0
 

Author Closing Comment

by:shieldsco
ID: 39278600
Thanks -- Works Good
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now