Update Query

[wyptaj]

Hi,
I have a table I have an excel spreadsheet with fields such as name, account number and current balance. I also have a database which I have built. There are many fields within the database but it includes the above i.e Name, account Number and current balance.
I have imported the spreadsheet into the database. I now want the system to use the Account numbers in the imported table and find the same account number in the database and then to update the current balance.

I have tried to do this by building an update query and also by relationship, but neither seem to work. Please can someone give me some guidance as to where I'm going wrong.
Many Thanks

 

[Pat Hartman]

If your query contains an aggregate functions - Sum(), First(), etc. or a Group By, it will not be updateable. Even though you are not actually trying to update the aggregated data.

Storing summary information such as balances is not recommended since it is too easy for them to get out of sync with the detail they summarize.

The only way to update a table with the summarized data is to build a temporary table to hold the summarized data and then use the temp table to update the permanent table.