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
  • Learn & ask questions
Solved

Use Update Query to Update First Occurence

Posted on 2013-06-25
9
268 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 100

Expert Comment

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

mlmcc
0
 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA modules import 4 57
Currency field that has too many digits after the decimal point. 16 49
2 subforms 1 main form 1 13
Access 2016 - combo box 3 17
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 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