Storing data calculated in a query in a table

[bbdsbd]

Hello, we are creating a new stock control system in access converting our old excel system but we are having problems with certain mechanics.
Our system currently looks at a table with orders in and finds the product code. The occurrence of the product code in the orders table is then counted to give a sum of sales for each individual product.

We would like to use this count of products to mark off stock components. But we are unsure how we would display this ‘used’ to the end user. We would like a total number of products sold to appear in a column in our products table which is separate to the sales table.


Would we use an update query to push the data to this column or is there another way to store the data outside of a query?

[ItsMe]

I recommend revisiting your table structure before you continue. It seems you are "committing spreadsheet" and your data is not "Normalized". You will always have trouble creating queries and maintain your data if your tables do not follow the rules of normalization. Perhaps you can start by creating an ERD and also work through various tutorials on Relational Database.
https://www.youtube.com/watch?v=-fQ-bRllhXc

[Homegrownandy]

Specific information regarding your product should all be in one table. If you want to use this information elsewhere you need to reference to it using primary/foreign keys.

Each product needs a primary key. A sequential auto number is easiest. Then other tables, like the one you have for sales would use that key to add whatever other information you want. It's hard to explain but follow the link Its me sent you and if you have any questions post them.

Andy,