Inventory control / on hand count

[Beth]

Hello,
I am working on an inherited Access 2000 database for sale and purchase of
items. Right now it is setup with a sales table and a purchases table, each
with the quantity and cost of the item (either sold or purchased).
I need to create a report of all the inventory items with a count of the
number purchased, the number sold, and the number on hand. It seems like an
easy solution, but the attempts I have made to query this don't come out to
the right counts.
Any help you can offer is greatly appreciated.
Here is the setup I have.

tblSales tblPurchases tblItems
ItemID ItemID ItemID
Quantity Quantity ItemName
Price CostEach PreferredVendor


Thanks is advance,
Beth

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Untested, but perhaps:

SELECT
I.ItemID,
I.ItemName,
Sum(P.Quantity) As Purchased,
Sum(S.Quantity) As Sold,
Sum(P.Quantity) - Sum(S.Quantity) As OnHand

FROM
(tblItems AS I INNER JOIN
tblPurchases AS P ON I.ItemID = P.ItemID)
LEFT JOIN tblSales As S ON I.ItemID = S.ItemID

GROUP BY
I.ItemID,
I.ItemName

This assumes that all the Purchases items are listed in the Items table
(INNER JOIN) and that the Sales contained some, or all, of the Items
(LEFT JOIN), and that all the items in the Sales table are also in the
Purchases table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREbkdoechKqOuFEgEQIXOwCfQ35jCth9L2pn4v231u7/36Vh4HAAoN2d
esC8KfMP+cZW/fkVH5iVMAOO
=o8an
-----END PGP SIGNATURE-----