Access - A query needed to keep inventory based on Sales
I can't seem to get this done, am frustrated as heck and would appreciate help...
I have two Access tables that I'm trying to query to get a running inventory and a quick glance of how much has been made on each against the cost of the items.
Table 1 - Named: Inventory
Fields wanted: ITEMNBR (the Item Number), COST (My Cost) CQTY (How many I have in Stock)
Table 2 - Named: Sales
Fields wanted: ItemID (the Item Number), Quantity (the quantity sold), PriceEa (price each the customer paid), TotalPrice (Quantity x PriceEa)
I want this:
ITEMNBR COST CQTY InStock(Inventory.CQTY - Sales.Quantity), SalesTotal (Sales.PriceEa x Sales.Quantity)
Now, here's the kicker. the Sales.PriceEa is not always the same for each item. And I have multiple lines for items in Inventory.ITEMNBR because my cost changes with each order that I make.
So, here's an example of my Sales Table:
ItemID PriceEa
AC2001 2.50
AC2001 0.75
AC2001 3.60
An Example of my Inventory Table:
ITEMNBR Cost CQTY
AC2001 1.50 2
AC2001 2.75 2
So I need Access to count the first two sales against the first AC2001 in the table and then, because that one is now out of stock, move to the second AC2001 and count the third sale again that.
I'd like it to look like this:
ITEMNBR Cost InStock SalesTotal
AC2001 1.50 0 3.25
AC2001 2.75 1 3.60
I hope that this makes sense and is possible...
Thanks in advance for your help.
I have two Access tables that I'm trying to query to get a running inventory and a quick glance of how much has been made on each against the cost of the items.
Table 1 - Named: Inventory
Fields wanted: ITEMNBR (the Item Number), COST (My Cost) CQTY (How many I have in Stock)
Table 2 - Named: Sales
Fields wanted: ItemID (the Item Number), Quantity (the quantity sold), PriceEa (price each the customer paid), TotalPrice (Quantity x PriceEa)
I want this:
ITEMNBR COST CQTY InStock(Inventory.CQTY - Sales.Quantity), SalesTotal (Sales.PriceEa x Sales.Quantity)
Now, here's the kicker. the Sales.PriceEa is not always the same for each item. And I have multiple lines for items in Inventory.ITEMNBR because my cost changes with each order that I make.
So, here's an example of my Sales Table:
ItemID PriceEa
AC2001 2.50
AC2001 0.75
AC2001 3.60
An Example of my Inventory Table:
ITEMNBR Cost CQTY
AC2001 1.50 2
AC2001 2.75 2
So I need Access to count the first two sales against the first AC2001 in the table and then, because that one is now out of stock, move to the second AC2001 and count the third sale again that.
I'd like it to look like this:
ITEMNBR Cost InStock SalesTotal
AC2001 1.50 0 3.25
AC2001 2.75 1 3.60
I hope that this makes sense and is possible...
Thanks in advance for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i guess the easiest thing to do would be to add a timestamp column (that is date+time)
that will hold for each row the date and time of the insert statement
that way you can keep track of which items were up to sell in every point in time
that will hold for each row the date and time of the insert statement
that way you can keep track of which items were up to sell in every point in time
ASKER