Not being too sure of how your Tables are organized, I've made some
guesses here and think I have a solution that will do what you want.
But I also suggest that you look at the "Northwind Traders" database for
ideas of what your database could look like. Also, in the New Database
wizard, the "Inventory Control" sample database and the "Order Entry"
sample database contain some ideas that you might want to use.
OK, here are the sample Tables I set up:
The first one you won't need if you sell only one kind of item, but
otherwise, this lists the products you have for sale and what the
quantity on hand was at the beginning of the year (or quarter, or month
-- however you keep track). There should be other fields here too, such
as unit price and supplier's phone number, but you'll see examples of
those in "Northwind Traders" and the other sample databases.
[Products] Table Datasheet View:
Product Name LevelAsOf1stOfYear
------------ ------------------
Thingumbobs 100
Widgets 300
The next Table contains your sales transactions, including what you
sold, how many units of it you sold, and the date of sale:
[item sales] Table Datasheet View:
item sales_ID Transaction Date ProductName UnitsSold
------------- ---------------- ----------- ---------
198729012 11/1/2005 Thingumbobs 2
-1044047323 11/23/2005 Thingumbobs 50
-1251813758 11/23/2005 Widgets 30
The next Table contains records of your purchases, where you added items
to your inventory, for later sale:
[stock entry] Table Datasheet View:
stock entry_ID Transaction Date ProductName Units Received
-------------- ---------------- ----------- --------------
-2100553847 11/23/2005 Widgets 15
-1527460641 11/20/2005 Widgets 7
423949475 11/20/2005 Widgets 135
473562730 11/1/2005 Thingumbobs 12
1346713878 11/24/2005 Widgets 120
Having set up these Tables (and I assume you will produce Forms to be
used for entering the values as you record the sales and purchases), I
set up a 3-part Query to display the number of items in your inventory
at any time, plus the current number of items.
The first Query combines the sales and purchase records into one list,
counting the sales as negative numbers (they reduce the number of items
you have on hand) and purchases as positive ones.
[Q_1_Transactions] SQL:
SELECT [item sales].[item sales_ID] AS Q_1_ID,
[item sales].ProductName, [item sales].TransactionDate,
-[item sales]![UnitsSold] AS NetChg
FROM [item sales]
UNION
SELECT [stock entry].[stock entry_ID],
[stock entry].ProductName, [stock entry].TransactionDate,
[stock entry].UnitsReceived
FROM [stock entry]
ORDER BY [item sales].ProductName,
[item sales].TransactionDate, Q_1_ID;
A list of these records shows all the transactions from both [item
sales] and [stock entry] Tables, listing how much of a change either way
each of the transactions made to your inventory:
[Q_1_Transactions] Query Datasheet View:
Q_1_ID ProductName TransactionDate NetChg
----------- ----------- --------------- ------
198729012 Thingumbobs 11/1/2005 -2
473562730 Thingumbobs 11/1/2005 12
-1044047323 Thingumbobs 11/23/2005 -50
-1527460641 Widgets 11/20/2005 7
423949475 Widgets 11/20/2005 135
-2100553847 Widgets 11/23/2005 15
-1251813758 Widgets 11/23/2005 -30
1346713878 Widgets 11/24/2005 120
The next Query uses these [NetChg] (meaning "net change") values to
compute a running total of items of each type of product that you have
on hand on a given date.
[Q_2_RunningLevels] SQL:
SELECT Products.ProductName, Products.LevelAsOf1stOfYear,
Q_1_Transactions.TransactionDate,
Sum(Q_1_Transactions_1.NetChg) AS RunningChg,
[LevelAsOf1stOfYear]+Nz([RunningChg]) AS CurrentLevel
FROM (Products INNER JOIN Q_1_Transactions
ON Products.ProductName = Q_1_Transactions.ProductName)
INNER JOIN Q_1_Transactions AS Q_1_Transactions_1
ON Q_1_Transactions.ProductName = Q_1_Transactions_1.ProductName
WHERE (((Q_1_Transactions_1.TransactionDate)
=[Q_1_Transactions]![TransactionDate])
AND ((Q_1_Transactions_1.Q_1_ID)<=[Q_1_Transactions]![Q_1_ID]))
OR (((Q_1_Transactions_1.TransactionDate)
<[Q_1_Transactions]![TransactionDate]))
GROUP BY Products.ProductName, Products.LevelAsOf1stOfYear,
Q_1_Transactions.TransactionDate, Q_1_Transactions.Q_1_ID
ORDER BY Products.ProductName, Q_1_Transactions.TransactionDate;
For example, the first 2 transactions are a sale of 2 "Thingumbobs" on
11/1/2005 and a purchase of 12 "Thingumbobs" on the same day. Since the
original number was 100, sale of 2 brings the total to 98, and purchase
of 12 brings it to 110, as seen in the Datasheet:
[Q_2_RunningLevels] Query Datasheet View:
Product Name LevelAsOf TransactionDate RunningChg CurrentLevel
1stOfYear
------------ --------- --------------- ---------- ------------
Thingumbobs 100 11/1/2005 -2 98
Thingumbobs 100 11/1/2005 10 110
Thingumbobs 100 11/23/2005 -40 60
Widgets 300 11/20/2005 7 307
Widgets 300 11/20/2005 142 442
Widgets 300 11/23/2005 157 457
Widgets 300 11/23/2005 127 427
Widgets 300 11/24/2005 247 547
The 3rd Query gives you a list of the current number of items of each
type that you have on hand now (that is, after all of the transactions
have been applied to the original inventory numbers):
[Q_3_FinalLevel] SQL:
SELECT Q_2_RunningLevels.ProductName,
Last(Q_2_RunningLevels.CurrentLevel) AS [Final Level]
FROM Q_2_RunningLevels
GROUP BY Q_2_RunningLevels.ProductName;
As you can see, these numbers match the last one in the list above for
each product, and I think this is what you were asking for.
[Q_3_FinalLevel] Query Datasheet View:
Product Name Final Level
------------ -----------
Thingumbobs 60
Widgets 547
(I used Access 2000 for this, but it should work about the same in
Access 2003.)
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
