Query/Update Sales Values for Individual Staff

[Parker278]

Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'.
This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie.
Is there any possible solution or is there a more simple way to solve the problem.
Many Thanks in advance Dan Parker

 

[ejstefl]

Typically, you would not want to store data such as this in the database, because it can change and become outdated rather quickly.

The preferred method is to simply calculate it "on demand" when you need it.

However, if you still wish to store it, you'll need to use an update query. You can simply make a new query, add the query you were able to make to count the employees. Then, add your employees table. Make sure they link by your EmployeeID field. Change the query to an update query. Bring down the "Number of Sales" field from the staff table, and in the "update to" line type in:

qryCount!CountField

replacing qryCount with the query name that counts the sales, and CountField with the field that contains the number of sales.

You could update all employees to 0 right before running the update query to get around the employees with 0 sales not appearing issue.