I suggest you don't do it that way.

Chances are you have a table of all the basic items in your company. To that
table, add a field called QtyOnHand. For all transactions of any item in the
table (receipt of goods, sales, etc.), add to or subtract from QtyOnHand.
That way, you don't have to contort your program to check sales against a
difficult-to-calculate on-hand quantity, as this quantity will be readily
available in your Item Master table.

HTH

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 <vjohns@alumni.caltech.edu>
  Please feel free to quote anything I say here.