totals query as recordsource of report

  • Thread starter Thread starter Jeff Wilkin
  • Start date Start date
J

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)
 
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
 
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.
 
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
 
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.
 
Back
Top