Quantity left in stock

[fido77777]

i have that product details table
contains different quantities for each product
(quantities i received in different dates)


i have that order details table
contains different quantities for same product
(quantities i sold in different dates)

now to find the amount left from each product
i need equation like this

(total quantity in stock) - (total quantity sold)


when i try to make it in a query
i couldn't do it without creating:

1-query to sum quantity in stock
2-query to sum quantity sold
3-query to find to subtract both quantities

when i try to make only one query to do all that
i get error message mostly about problem with aggregate function

any solution?

 

[RuralGuy]

Maybe this link will be helpful.

 

[fido77777]

thanks for your help
it was really helpful

but i am doing it as an example on queries
my only problem here:
that i want to replace the 3 queries with only one select query to get the job done
is it possible or not?

 

[Pat Hartman]

Is all the data in a single transaction table? If it is, you can use a totals query to sum the additions and subtractions. If the amounts are recorded as absolute values, you'll need to multiply the subtractions by -1 to get them to actually subtract.

If the data is in two tables, then three queries are required.