Inventory level in report

J

JT

I am attempting to run a report to give an inventory level for an inventory
of forms

I have a starting count for my forms, forms are added and sent out at random
intervals. When the report is opened. Here is all I need to see

Form # Form Name Current Inventory Level

I have created 2 queries that run successfully - one to add new receipts to
start inv and one to subtract forms sent out. But, I am going crazy trying to
get a accurtate stock level - Start Inventory - amount sent out + additional
receipts. It seems that it should be obvious, but I'm just missing it.

Here are the tables I have

tblForms:
FormNumber
FormName
StartInventory

tblAddInv:
ID(autonumber)
DateAdded
FormNumber
AmountAdded

tblOrderDetails: (subform for data entry)
OrderID(autonumber)
LineNumber (for subform use - multiple forms per order)
FormNumber
QtySent
DateShipped


Thanks for any help
 
L

Larry Linson

If you'd clarify how you represent the start number, the additions, and the
distributions, perhaps someone would have a better chance of assisting.

If there is just one "forms inventory table" with a field for add/remove,
with additions being a positive value and removals being a negative, you
should be able to use a totals query that simply sums that field, grouping
on the form identification. The "start number" would, in such a design, just
be an addition, thus a positive value.

Larry Linson
Microsoft Access MVP
 
J

JT

Sorry, I have the three separate tables listed at the bottom of my original
post that relate to inventory - the start number is a manual count that was
done. This is reflected in the tblForms field StartInventory. The additions
are located in the tblAddInv table in the AmountAdded field. The
distributions are in the tblOrderDetails form in the QtySent field. Here is
a scenario if it helps to understand flow.

1- Manual count was done by clerks for start values in the supply room, I
populated the tblForms with these amounts.

2- An order comes in for forms - clerk fills in a form that has a subform
that populates tblOrderDetails table.

3- Receipt of forms from publisher - clerk fills in a form that populates
the tblAddInv table

I have several reports that run off of these, who received what forms, what
forms were sent to whom and I need to have an inventory to tell the supply
room what levels are at any given point in time. Eventually adding an
automated reminder when we reach a certain level to reorder. But, I will
tackle that one later right now I just need to inventory level report -

Thanks!
 
J

John Spencer (MVP)

Assuming FormNumber is a text field. If form number is a number field then drop
the apostrophes (single quotes) in the DSum functions.

SELECT tblForms.FormName,
StartInventory
-NZ(DSum("AmountAdded","tblAddInv",""FormNumber='" & FormNumber & "'"),0)
-NZ(DSum("QtySent","tblOrderDetails","FormNumber='" & FormNumber & "'"),0) as CurrentInventory
FROM tblForms

This may be slow. If it is too slow, post back and we can come up with a more
complex solution.
 
J

JT

Thanks John, I have been on another project so I wasn't able to revisit
this. I tried many times Friday and this morning. the solution and I get
the error "The syntax of subquery in this expression is incorrect - Check
subquery syntax and enclose subquery in parenthesis" I tried to enclose the
entire thing in parens and then I get expression too long error - any
suggestions? Thanks!
 
Top