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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim P.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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.