Inventory report

[pa3329]

I am a bit stuck on an inventory report. The database I am working on keeps track of chemicals including the amount of chemicals purchased (Acquisitions), disposed, as well as the initial quantity on hand. Using these three figures I am able to do many things but I can't figure out how to get the inventory on hand at a specific date. I want to be able to get the inventory for an individual chemical or all chemicals at once. I have attached the db for you to look at. I would appreciate any help you can give me.

 

[mhartman]

Hello:

First of all, I would like to complement you on fine construction of your database. Its very professional and looks beautiful.
'
To your question, It looks to me when I open your form Inventory Report, this is doing what your ask, or I may not be understanding your question. You can make a report using this form format.
Regards
Mark

 

[pa3329]

Thanks for the compliments although there is quite a lot of formatting I still need to do. My form does not filter according to the ending date. For instance if you pick the ending date to be 8/30/06 it will include transactions made after that date (it won't cut off the data). I know that the product computer is one of those instances.

 

[mhartman]

Hello
Start your database and open the frmInventoryReport form. Place 08-08-06 as the beginning date. Place 08-22-06 as the end date. Then while this form is loaded run the query called Ztest in the query section.
'
Is this the type of thing your after? It limits the records by two criteria.
Regards
Mark

 

[whatever]

Nice DB,

I think the issue is with what you are calculating on each row.

I think for each product group you should have an opening position then you should add each aquisition and remove any disposal as a row an then have a group total which should be your on hand amount..

The starting position for each group is key and appears to have at least two ways of calculating it,

either

a: ProductQuantity from the product table + aquisitioned [quantity] from tblAcqDetail less invoiced [Quantity] from tblInvoiceDetail until the start date of the report.

or

b: latest stock take + aqusitioned (quantity) from tblAcqDetail less Quantity from tblInvoiceDetail between the stock take date and the start date.

I will hopefuly have a further look tomorrow and PM you.



HTH


Kevin

 

[pa3329]

Hello
Start your database and open the frmInventoryReport form. Place 08-08-06 as the beginning date. Place 08-22-06 as the end date. Then while this form is loaded run the query called Ztest in the query section.
'
Is this the type of thing your after? It limits the records by two criteria.
Regards
Mark

I guess what I need is limiting it by three criteria. I need it to limit by transactions from 'the beginning of time', the ending date, and the particular chemical (or all chemicals). When you open the inventory report you will select the ending date (the beginning date is already entered) and a chemical (or all chemicals). What the report does is gives you the current inventory on hand. I don't need anything other than the current inventory. If it would be easier I can live with the inventory of all chemicals. In that case you would only select an ending date and that's it.

 

[pa3329]

Nice DB,

I think the issue is with what you are calculating on each row.

a: ProductQuantity from the product table + aquisitioned [quantity] from tblAcqDetail less invoiced [Quantity] from tblInvoiceDetail until the start date of the report.

Thanks for the kind words. Your a) is what I am looking for. It seemed nice and easy when I outlined it on paper but it is another thing when I try to put it in the report. I would greatly appreciate any help you can give me.

 

[whatever]

The problem is where you start from for each product and how each of the rows work.

You need to decide what the definition of the starting position is, is it:

1. Last stock take + (acqs - invoice) since stock take until start date, or

2. Opening position + (acqs -invoice) until start date.

you can then add a row for each (acqs -invoice) until the end date.

Giving a closing position as the starting amount + rows which is the amount in hand you are after.

NB This is further complicated if the last stock take is after the start date of the report.

I think the stock take would normally take precedence however this is probably too detailed for the forum, if you want PM your email address and we can hopefully sort you out and post the answer back here.



HTH


kevin

 

[pa3329]

whatever, I PMed you my email address and a little bit of an explanation.