Updating a table from a query

[sir_dan_mitchell]

I am designing a database to store and calculate products + prices in a video shop. I have created a query to calculate amound due etc, but i want to create a table for recording all transactions made. In the query you can to do this using the make table option, but when you update it again it deletes the previous data. HELP ME PLEASE!

Also explain as simply as possibly.

Thanks

 

[adz2013]

I am not exactly sure I understand what you are asking, but I think you want an append query. Change the make table query to an append one and the old records will not be erased.

HTH

 

[sir_dan_mitchell]

Hi, Thats great it has 99% solved my problem. The only thing now is that I have duplicate records in the table appearing. how do I remove these duplicates?

Thanks

 

[Engelking]

You have to index whatever unique field you have in the table to not allow duplicates (or maybe multiple fields), then they will automatically be deleted when you do the append query.

 

[adz2013]

One way to prevent them is to have a primary key and anything that has the same primary key will not go into the table. Then you have no dups. To get rid of the ones you have create a duplicate query using the wizard and delete them.

 

[sir_dan_mitchell]

Hey again,

AMAZING it all works, accept i get an error.

"Vidz Rentals can't append all the records in the append query"

Vidz Rentals set 0 field(s) to Null due to a type conversion failure, and it didnt add to record(s) to the table due to key violations, o record(s) due to lock violations, and 0 record(s) due to validation rule violations.

Do you want to run the query anyway?"


When i run the query anyway it does seem to work. but can i get rid of this error message?

 

[adz2013]

The message is fine it is just letting you know that some records can't go in because they would create dups. If you are going to run it on a regular basis create a macro and the first field should be
SetWarrings NO
the next field should be
OpenQuery and you can have all the queries run in one click by putting them all in one macro.

HTH

 

[Rich]

You should be aware that storing calculated fields in this way is going to cause you more problems than you think you've solved.

 

[Pat Hartman]

You seem to have already stored all transactions. You don't need to store them again.

You should however be storing the price of each item when the transaction is made. Right now it sounds like you look up the price but don't store it. This is one of those subtle areas where it is not only acceptable to store reference data but it is encouraged. The price for an item will change over time therefore you cannot use the current price to calculate a value for prior transactions since the price in effect when they occured might have been different.