Update Query - Making sure this is correct

[rbraitz]

I am trying to write an update query and not having much luck. I have a 'history' table of transactions produced by Quickbooks that was imported into excel/and then access. Let's call this transactions_main (in this table, I have 2 columns that I would like to value based upon the values in a separate table, called banktrans. In the banktrans table, I have a cleared date, checknum, and a cleareddate. I want to update the values from banktrans into the transactions_main and only populate the data in the columns cleareddate and clearedamount. The filed that would link them in theory is checknum. when i run the update query, it deletes the data in the banktrans table which I'm not understanding why. Thank you in advance.

[June7]

Why have data in both tables and not just retrieve in query joining records?

Provide your query SQL statement. Sample tables might be helpful. If you want to provide db for analysis, follow instructions at bottom of my post.

[rbraitz]

1. Thank you for your reply.
2. I want to keep them separate because I will write a Macro to import them into the banktrans table on a daily basis.
3. I will have automated reports via a Macro.
4. my SQL is all messed up since I've been messing with it, sorry.

[June7]

You were missing JOIN clause and field references wrong

UPDATE transaction_main INNER JOIN banktrans ON transaction_main.checknum = banktrans.checknum SET transaction_main.cleareddate = [banktrans].[cleareddate], transaction_main.clearedamount = [banktrans].[amount];

Still don't understand why need to duplicate data. Yes, import data daily - can still build a query joining tables to retrieve data. That is what is required for the update action anyway. Is there something else I should know about process?

[rbraitz]

MANY MANY Thanks!!!!! it works.....!!! wahoo...