Link to home
Create AccountLog in
Avatar of stoneycurtis
stoneycurtis

asked on

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
Avatar of als315
als315
Flag of Russian Federation image

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.
Avatar of stoneycurtis
stoneycurtis

ASKER

@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
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.
ASKER CERTIFIED SOLUTION
Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@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?
That should work as well.