How do I create a query to display the stock remaining of products?

[robjh]

Hi there! :)

I am doing a school project in Microsoft Access, but I am having trouble setting up one of my queries....

The two relevant tables are "Products" and "Purchases"

Products
Product ID
Name
Type
Make Price
Supplier ID
Stock When Last Ordered (remaining stock plus quantity ordered when last ordered from supplier)

Purchases
Purchase ID
Date
Product ID
Patient ID
Quantity Purchased

Is there a way to write / run a query to display the stock remaining for each product in the "Products" table (perhaps to subtract the number of times a product has been purchased from the "Stock When Last Ordered" field?)

Thanks so much! :)

[ChipR]

I don't see any way to make use of "Stock When Last Ordered" without other information, like when that was. You could have a field like OriginalStockQuantity in your Products table, and a table to record products and quantities received from a supplier. Then you have a query for total received per product, another for total purchased per product, and a final query with the remaining result.

[robjh]

Thanks! I've updated the tables, but I'm still not sure how to write the suggested queries as i am unfamiliar with SQL language. Help would be greatly appreciated.
:)

[ChipR]

If you are using the query design view, right click somewhere in the fields in the bottom portion and select Totals. Then, select the Product ID field with Group By, and another field to Sum. Access Help has a very lengthy topic on this, but I recommend experimenting, because it's pretty simple to start with. Once you get it, you can always switch to SQL view to see how the query was written.