query calculating remaining stock

[kraasty]

Hello,
I have three tables in a simple inventory database,
table_items, table_imports & table_exports with the following fields:

table_items:
item_ID (Primary Key)
item_quantity
table_imports:
item_ID (related to table_items)
import_quantity
table_exports:
item_ID (related to table_items)
export_quantity

How can I create a query using the "access query builder" to
calculate the remaining quantity for a specific item ?

The query will have to add all the fields from import_quantity
to the item_quantity and subtract all the fields from export_quantity

Thank you

 

[Guest]

Check out Allen Browne's sample:

Inventory Control: Quantity on Hand
http://allenbrowne.com/AppInventory.html


Tom
_______________________________________

:

Hello,
I have three tables in a simple inventory database,
table_items, table_imports & table_exports with the following fields:

table_items:
item_ID (Primary Key)
item_quantity
table_imports:
item_ID (related to table_items)
import_quantity
table_exports:
item_ID (related to table_items)
export_quantity

How can I create a query using the "access query builder" to
calculate the remaining quantity for a specific item ?

The query will have to add all the fields from import_quantity
to the item_quantity and subtract all the fields from export_quantity

Thank you

 

[kraasty]

This is what I was looking for.
In fact, I found the whole Allen Browne?s tips page very helpful.

Thank you Tom.

 

[Guest]

You're welcome. I'm glad I could help. There are lots of people who are
regulars on this newsgroup. I'm sure all of them, including myself, share
your opinion on how very useful Allen's site is.

Tom
_____________________________________

:

This is what I was looking for.
In fact, I found the whole Allen Browne's tips page very helpful.

Thank you Tom.