totals query as recordsource of report

[Jeff Wilkin]

Hi All,

I'm working on a small inventory .mdb to keep track of about 900 different
products any of which can be located in up to 9 different storage areas. I
do periodic stock takes which are then recorded in a m:m junction table with
a date stamp. A qry based on this table is the recordsource for my Quarterly
and EoY reports because I have to physically count everything then anyhow -
this is easy. But for an interim report, I need a totals qry that lists each
product in each storage area and which also takes into account all
deliveries and transfers since the last stock-take date. This has me
absolutely baffled. I don't even know where to begin. Does anyone have any
suggestions for an SQL idiot?
Thanks for even reading this far,
Jeff

Report structured as follows:
[StorageID] {Grouping}
[ProdID] - [Volume] - [QtyOH] - [IndPackID] - [TotVal = QtyOH *
ProdCost]

Looks like:
Refridgerator 1
Heineken 0,33l 3000 Bottle 2.313,00 ?
Löwenbräu 50L 10 Keg 738,00 ?

pertinent table and field structures as follows: all FK's are long integers

tblStorage:
StorageID (PK auto)
Storage

tblProducts:
ProdID (PK auto)
Product
Volume 'text ie 50 Liters
IndPackID (FK) ' individual packaging type ie bottle, can, keg
ProdCost 'currency - cost per individual packaging type
CategoryID (FK)

tblCategories:
CategoryID (PK)
Category ' beverage, food item, cleaning supplies

tblPackaging:
PackID (PK auto)
Packaging

tblProductStorage: ' M:M junction tbl *****
DateStamp ' date/time - short - updates via code from form after QtyLast
updated
StorageID (FK & comp.PK)
ProdID (FK & comp.PK)
QtyLast ' double
IndPackID (FK)

tblTransfers:
TransferID (PK auto)
TransDate ' date/time - short
StorageID (FK) ' location INTO which item is received

tblTransDetails:
TDetailsID (PK auto)
TransferID (FK)
StorageID (FK) ' location FROM which item is removed
ProdID (FK)
QtyOut ' double
IndPackID (FK)

tblDeliveries:
DeliveryID (PK auto)
DeliveryDate ' date/time - short

tblDeliveryDetails:
DDetails (PK auto)
DeliveryID (FK)
StorageID (FK) 'location into which item is received
ProdID (FK)
QtyIn ' double
IndPackID (FK)

 

[Allen Browne]

Jeff, there is an example in this link of how to put together a function to
read the value from last stocktake, add the acquisitions, and subtract the
sales:
http://allenbrowne.com/AppInventory.html

 

[Jeff Wilkin]

Hi Allen,
thanks for the link. I've actually looked at it before, but I have no idea
how or where to use the function to supply to a report. Could you steer me
in the right direction? And how would I incorporate a StorageID?
Thanks for any assistance you can provide,
Jeff

 

[Allen Browne]

To use the function:
1. Copy from the web page.
2. Click the Modules tab of your Database window, and click New.
Access opens a code window.

3. Paste.

4. If you are using Access 2000 or 2002, choose References from the Tools
menu, and check the box beside:
Microsoft DAO 3.6 Library.

5. Choose Compile from the Debug menu to ensure Access understands the code.

6. Save the module. (Module1 will do for a name.)

You can now use the function like any other in Access. For example, if you
create a report of your products, and you have a ProductID field, you could
put a text box on your report and set its Control Source to:
=OnHand([ProductID])

The function (and the data structure) would need some modification to handle
multiple storage areas. You would need to be familiar with VBA coding to
achieve that.

 

[Jeff Wilkin]

Hi Allen,
Thanks for the fast reply. Everything compiled just fine with the modified
function. I declared vStorageID as Variant and dimmed lngStorage and added
that to the WHERE clauses. Sound right so far?
Thanks again,
Jeff

 

[Allen Browne]

That's fine.

Presumably you need the storage location field in your tables to indicate
stock going in and out, and you will also stocktake at each location.

 

[Jeff Wilkin]

Thanks once again for your help. It works.
Jeff