Solved

Access UPDATE query

Posted on 2013-05-18
7
266 Views
Last Modified: 2013-06-04
I'm importing updated data from a .csv/.txt file into my database and then running an update query to bring data up-to-date. The data gets updated correctly only if there is no change in the order of the data in the source .csv/.txt file. I want to update the data by relevant data matching, not by position.
The SQL:
UPDATE tblPositionSymbol INNER JOIN (tblShrtName INNER JOIN (Newposition INNER JOIN 
tblPosition ON Newposition.ID = tblPosition.PositionID) ON tblShrtName.ID = 
tblPosition.tblShrtName_ID) ON tblPositionSymbol.ID = tblPosition.tblPositionSymbol_ID 
SET tblPosition.quantity = [newposition].[quantity], tblPosition.cost = [newposition].[cost], 
tblPosition.[$gain/loss] = [newposition].[$gain/loss], tblPosition.[mkt value] = 
[newposition].[mkt value], tblPositionSymbol.[as of date] = [newposition].[as of date];

Open in new window

I'm also attaching a jpg that shows the relationships.
I need criteria to find the matching data between the [NewPosition].[symbol] and [tblPositionSymbol].[symbol].
Thanks in advance for any help or suggestions
query.jpg
0
Comment
Question by:stoneycurtis
  • 2
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39178168
If you have headers in your csv (txt) files, you should have no problems. Can you upload sample DB and csv files with this problem? You can fill these files with some dummy data.
0
 

Author Comment

by:stoneycurtis
ID: 39178640
@als315
Thanks, I've attached a zip file containing the sample accdb and a copy of the "Newposition" file that gets imported into the 'Newposition' table.
The qryAcct_Pos_Client is what I intend to base reports/form on.

Let me know if you have any questions.
Thanks again
sample-accdb.ZIP
0
 
LVL 39

Expert Comment

by:als315
ID: 39179257
Can you give more details about your data? If you like to update your existing table, you should have one-to-one relation between existing and imported tables. It could not be an autonumber field - it will be different each time.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39179629
You can have compound primary keys and compound joins. It looks like [symbol] and [shrt name]  is it. If there is another column I missed it in my quick look. Try something like this:

UPDATE tblPositionSymbol INNER JOIN (tblShrtName INNER JOIN (Newposition INNER JOIN 
tblPosition ON Newposition.ID = tblPosition.PositionID) ON tblShrtName.ID = 
tblPosition.tblShrtName_ID) ON tblPositionSymbol.[symbol] = tblPosition.[symbol]
and tblPositionSymbol.[shrt name] = tblPosition.[shrt name]
SET tblPosition.quantity = [newposition].[quantity], tblPosition.cost = [newposition].[cost], 
tblPosition.[$gain/loss] = [newposition].[$gain/loss], tblPosition.[mkt value] = 
[newposition].[mkt value], tblPositionSymbol.[as of date] = [newposition].[as of date];
                                  

Open in new window

0
 

Author Comment

by:stoneycurtis
ID: 39181847
@jimpen and als315
There are duplicates of [symbol] and duplicates of [shrt name], however, the combination of symbol+shrt name is unique.
What if I add a field to the NewPosition table called "symbname" and populate it with an UPDATE query that combines 'symbol' and 'shrt name' and then set that field as the Primary Key.
I can add a similar field to the tblPosition and then use the WHERE clause to match the two fields for the UPDATE query.
Comments?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39181894
That should work as well.
0

Featured Post

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

Join & Write a Comment

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

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