E
esparzaone
Please Help I am working on an inventory system. I need a report to
show/calculate the
following:
first grab information by date when opened (which I have done)
Then enter another criteria "account number" (which I have done)
Then enter"Location" ( which I have done)
I want it to add how many units for that time period, by account number,
and location and then multiply by the sum of all the prices. I have enclosed
what I have. I am lost.
My overall idea is when i enter my criterias it will show date range -
Account number - location - Total$ (Total $ of units on hand) so then I can
post this number to how many $ in inventory we have.
I am working on an inventory system. I need a report to show/calculate the
following:
first grab information by date when opened (which I have done)
Then enter another criteria "account number" (which I have done)
Then enter"Location" ( which I have done)
I want it to add how many units for that time period, by account number,
and location and then multiply by the sum of all the prices. I have enclosed
what I have. I am lost.
My overall idea is when i enter my criterias it will show date range -
Account number - location - Total$ (Total $ of units on hand) so then I can
post this number to how many $ in inventory we have.
SELECT DISTINCTROW Products.[Account Name], [Inventory
Transactions].Location, Sum([Inventory Transactions].Price) AS SumOfPrice,
[Inventory Transactions].ProductName, Products.ProductDescription
FROM Products RIGHT JOIN [Inventory Transactions] ON Products.ProductName =
[Inventory Transactions].ProductName
WHERE ((([Inventory Transactions].Date)>=[forms]![Report Date
Range]![BeginDate] And ([Inventory Transactions].Date)<=[forms]![Report Date
Range]![EndDate]))
GROUP BY Products.[Account Name], [Inventory Transactions].Location,
[Inventory Transactions].ProductName, Products.ProductDescription
HAVING (((Products.[Account Name])=[Enter Account Name]) AND (([Inventory
Transactions].Location)=[Enter Location]))
ORDER BY [Inventory Transactions].Location;
Then I thought to add this
Inv Value: Sum(nz([Inventory Transactions.#received]))-(nz([Inventory
Transactions.#use]))-(nz([inventory Transactions.waste]))
show/calculate the
following:
first grab information by date when opened (which I have done)
Then enter another criteria "account number" (which I have done)
Then enter"Location" ( which I have done)
I want it to add how many units for that time period, by account number,
and location and then multiply by the sum of all the prices. I have enclosed
what I have. I am lost.
My overall idea is when i enter my criterias it will show date range -
Account number - location - Total$ (Total $ of units on hand) so then I can
post this number to how many $ in inventory we have.
I am working on an inventory system. I need a report to show/calculate the
following:
first grab information by date when opened (which I have done)
Then enter another criteria "account number" (which I have done)
Then enter"Location" ( which I have done)
I want it to add how many units for that time period, by account number,
and location and then multiply by the sum of all the prices. I have enclosed
what I have. I am lost.
My overall idea is when i enter my criterias it will show date range -
Account number - location - Total$ (Total $ of units on hand) so then I can
post this number to how many $ in inventory we have.
SELECT DISTINCTROW Products.[Account Name], [Inventory
Transactions].Location, Sum([Inventory Transactions].Price) AS SumOfPrice,
[Inventory Transactions].ProductName, Products.ProductDescription
FROM Products RIGHT JOIN [Inventory Transactions] ON Products.ProductName =
[Inventory Transactions].ProductName
WHERE ((([Inventory Transactions].Date)>=[forms]![Report Date
Range]![BeginDate] And ([Inventory Transactions].Date)<=[forms]![Report Date
Range]![EndDate]))
GROUP BY Products.[Account Name], [Inventory Transactions].Location,
[Inventory Transactions].ProductName, Products.ProductDescription
HAVING (((Products.[Account Name])=[Enter Account Name]) AND (([Inventory
Transactions].Location)=[Enter Location]))
ORDER BY [Inventory Transactions].Location;
Then I thought to add this
Inv Value: Sum(nz([Inventory Transactions.#received]))-(nz([Inventory
Transactions.#use]))-(nz([inventory Transactions.waste]))