calculating On Hand Quantity

[hbm001]

Hi,



I am having a problem in calculating the on hand quantity for a database recording books inventory for a small book store.

So far I have 3 tables
Table 1- Books
Table 2 - Incoming Inventory (recorded per transaction)
Table 3 - Outgoing Sales (recorded per transaction)

I have a query calulating the total books incoming sorted by title and month , and another query for total books outgoing sorted by title and month.

Now, I need to calculate on hand quantity , the filed for stock at physical count is in the "Books" table, and the total books incoming is in query 1, total books outgoing in query 2. How can i combine all 3 data items to get a formula for the on hand quantity in a new query.

I've tried Alllen Browne's On hand quantity method - but its too complicated!

Please help! First time using access.

[pbaldy]

Allen's method is a bit complicated. If you have the 2 queries already, add them along with the Books table to a new query. Edit the join between the queries and the Books table so you get all records from the Books table. That should let you bring the total in and out on one line with each book. That should let you calculate the on hand amount (you may need the Nz() function).

[hbm001]

thank you for the super quick reply.
problem is that I can't use a previous query result in my new query. It won't let me add any field unless its a table. Is the join function going to solve this issue?

Also, what does the Nz() function do?

[pbaldy]

You can certainly use one query in another. Can you post the db here, or a representative sample?

The join will make sure all products are included, whether or not they have records in the incoming or outgoing tables.

[hbm001]

The file is currently too big, sorry I can't send the full file, but here are the printscreens - hope they help.

I want the new query to have the field "Stock" from tbl.Books , and results from the crosstab query.inventory for each title, for column 1,(month 1) and exactly the same required from query.sales for each title, or column 1 (month 1)

database printscreen.pdf

You can certainly useThe join will make sure all products are included, whether or not they have records in the incoming or outgoing tables.

one query in another. Can you post the db here, or a representative sample?

[pbaldy]

Have you tried with the two queries not being crosstabs? Using a date criteria on both should let you get the inventory for any given point in time.

[hbm001]

Hi,

Could I please send you the database by email to help me out? I don't know how to do the join function. I tried to google a link that would teach me this, however none of them give you the option to join a table and query together.

By the way, the option for removing crosstabs worked! thanks

Have you tried with the two queries not being crosstabs? Using a date criteria on both should let you get the inventory for any given point in time.

[pbaldy]

Sure:

deleted to prevent spam

[hbm001]

Thanks,
just emailed

[pbaldy]

What is your expected result from the test db? I assume sales are transactions going out and inventory is coming in?

[pbaldy]

Is this the expected result?

[hbm001]

Yes this is very close to what im
looking for, but basically the on hand quantity must also account for the field "stock" in table books. So on hand quantity = stock+ inventory - sales.

Also would it be simple to calculate if the book is required to be reordered if each book has a different re-order point?

[pbaldy]

See if this does what you want. Adding a reorder point would be simple, presuming it's a field in the books table.

[hbm001]

Thank you! This is exactly it! And for re-order point, if it is a specified level in the books table, do I just create a new query subtracting Quantity on Hand with the re-order quantity? or is there a better way?

See if this does what you want. Adding a reorder point would be simple, presuming it's a field in the books table.

[pbaldy]

It can be in the same query or another. You'd simply be comparing on hand to the reorder point. If on hand is lower, it's time to reorder.