Access UPDATE query

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
stoneycurtisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim P.Connect With a Mentor Commented:
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
 
als315Commented:
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
 
stoneycurtisAuthor Commented:
@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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
als315Commented:
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
 
stoneycurtisAuthor Commented:
@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
 
Jim P.Commented:
That should work as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.