need differences between two tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

klnmis - 24 Jan 2007 20:41 GMT

I have one table that contains the purchases for the year. Fields
are:item_nbr, Qty and period. I have another table that contains the sales
for the year. Fields are: item_nbr, Qty and Period.
Is there a way to make a report that shows qty purchased less qty sold for
each period for each item? The item_nbr field in each table reflects the same
item.
The problem I am having is: if an item is not purchased during a period it
does not show on the report for that period. I would like all items with
activity for a period to show on the report. I think this should be easier
than I am making this out to be.
Thanks in advance.

Reply to this Message

Pendragon - 25 Jan 2007 20:33 GMT

Use this for your query (test it, of course):

SELECT Purchase.item_nbr, Purchase.Qty, Iif(isnull(Sold.Qty),0,Sold.Qty),
Purchase.Period
FROM Purchase LEFT JOIN Sold ON Purchase.item_nbr = Sold.item_nbr;

where Purchase is the name of the purchases table, Sold is the name of the
sales table. Also, if you have default values of 0 for Qty, then you can
eliminate the IIF statement and simply have that as Sold.Qty. Set your
criteria for inputting a date range under Purchase.period (something like

>=[Enter start date] And <=[Enter end date]).

Your query generates Item Number, Purchase Qty, Sold Qty, Period. You can
add a text box to your report that calculates the difference between the two
Qty amounts.

HTH

> I have one table that contains the purchases for the year. Fields
> are:item_nbr, Qty and period. I have another table that contains the sales
[quoted text clipped - 7 lines]
> than I am making this out to be.
> Thanks in advance.

Reply to this Message