Refunds Nightmare

[Guest]

Been stuck on this for ages...My problem is, I need to subtract a total of
refunds from a total of purchases within a certain time period from different
suppliers.

So far I have the total purchases by Suppliers using the following sql :

SELECT Purchase.Supplier_ID, Count(Purchase.Stock_ID) AS CountOfStock_ID,
Sum(Stock.[Supplied Price ex-VAT]) AS [SumOfSupplied Price ex-VAT],
Purchase.Date, Sum(Purchase.[Sold Price]) AS [SumOfSold Price]
FROM Supplier INNER JOIN (Stock INNER JOIN Purchase ON Stock.Stock_ID =
Purchase.Stock_ID) ON Supplier.Supplier_ID = Stock.[Supplier_ ID]
GROUP BY Purchase.Supplier_ID, Purchase.Date
UNION SELECT [Supplier_ID], 0, 0, 0, 0
From Supplier
GROUP BY Supplier_ID;

This is to give the cash owed to suppliers.
I need to take out the value of refunded stock within a time frame - Between
Dates.
I have the count/union there as I sometimes there is a refund with no
purchase in a time-frame as vice-versa.

Is there a way? Am I just going about this a long way? HELP!!

 

[Douglas J Steele]

You don't actually state what the problem is, but one thing I see is that
you'll end up with two rows for those suppliers for whom there are refunds.

You can either do another Totals query on the query you show below, so that
you consolidate those duplicate rows into one, or you can try using a LEFT
JOIN between Supplier and Purchase, so that you get back a row for each
Supplier, whether or not there are any purchases.

Or is your issue that you don't know how to incorporate the refunds into
that query? If you have a query that returns all known refunds for the
period, then yes, you could UNION that query with the one you're showing,
then create a Totals query on that new UNION query to add purchases and
refunds together.

 

[Guest]

Cheers mate reworked a few queries and used a join for the final totals,
using null values for the results.