Solved

Access UPDATE query

Posted on 2013-05-18
7
277 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

911 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

19 Experts available now in Live!

Get 1:1 Help Now