oh yeah, that was a bad solution - as in "doesn't work". it is, however, a
good example of what happens when you don't test a solution thoroughly, with
multiple records.
try this instead (and if it doesn't work, i'll go find a rock to crawl under
<g>)
SELECT A.FormNumber, tblForms.FormName, Sum(A.StartInventory) AS
CurrentInventory
FROM tblForms LEFT JOIN (SELECT FormNumber, StartInventory
FROM tblForms
UNION ALL SELECT FormNumber, AmountAdded
FROM tblAddInv
UNION ALL SELECT FormNumber, [QuantitySent]-([QuantitySent]*2) AS Minus
FROM tblOrderDetails) AS A ON tblForms.FormNumber = A.FormNumber
GROUP BY A.FormNumber, tblForms.FormName;
hth
JT said:
Thanks Tina - it looks like it works, but when I check numbers they are
incorrect. for example one of the forms
StartInv = 402, AmountAdded = 110, QtySent = 23, 6 (two separate orders) -
Current Inventory should be 483 - it is returning 995
it seems that if there is more than one entry for QtySent is when a problem
occurs
Thanks so much for your help!
:
try this query instead, as
SELECT tblForms.FormNumber,
nz(Sum([StartInventory]),0)+Nz(Sum([AmountAdded]),0)-Nz(Sum([QuantitySent]),
0) AS CurrentInventory
FROM (tblForms LEFT JOIN tblAddInv ON tblForms.FormNumber =
tblAddInv.FormNumber) LEFT JOIN tblOrderDetails ON tblForms.FormNumber =
tblOrderDetails.FormNumber
GROUP BY tblForms.FormNumber;
i used the table and field names that you listed in your original
post,
so
if those names are not correct, you'll need to change them in the SQL
statement above.
hth
Please help - I think this dropped off the radar because I
couldn't
look
at
it for a week. Here is the original post and replies. Thanks! - I could
not
get solution to work
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!
:
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.
JT wrote:
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
Was this post helpful to you?
