S
sbatschelet
I have been banging my head on this one for 2 days I might just need a
fresh set of eyes. This query lists all of the sales categories which
are listed in POSCat, more specifically POSCat.Description. It then
sums these categories as they appear in the sales history table
InvLine. What I like about this layout is that even if there is a null
sum value for a category it still will be listed and I can use
something like iif to make it $0.00.
Select POSCat.*,(Select Sum(InvLine.Price) From InvLine Where
POSCat.Description = InvLine.Cat) as Totals
FROM POSCat
The recordset looks like this
cat1 sumcat1
cat2 sumcat2
Now although that works on some levels its very limited and only gives
me a total for the table. What I really need is to verify that the
item has been paid for by comparing the invoice total to invoice paid,
also be able to search by date. What I came up with is this:
SELECT InvLine.Cat, SUM(InvLine.Price) AS Total
FROM Payments
INNER JOIN ((InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId)
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
The recordset looks like this if there was values only in those
categories 1, 4, 5
cat1 sumcat1
cat4 sumcat4
cat5 sumcat5
Which works great except it only lists the categories which are not
null but I would like to list all of them like the first query does.
After a little help I realized that I needed to do an Outer Join
I tried this a few diffent ways but Access is complaining that the join
is not supported, so my SQL must be setup wrong. Here is what I have
so far, again any help is appreciated.
SELECT POSCat.Description, Sum(Payments.Value) AS Total
FROM POSCat LEFT OUTER JOIN (Payments INNER JOIN ((InvLine INNER JOIN
Invoice ON InvLine.IId = Invoice.IId) INNER JOIN PaySplit ON
Invoice.IId = PaySplit.IId) ON Payments.PAYId = PaySplit.PayId) ON
POSCat.Description = InvLine.Cat
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate Between
#3/20/2006# And #3/22/2006#
GROUP BY POSCat.Description
ORDER BY POSCat.Description Asc
fresh set of eyes. This query lists all of the sales categories which
are listed in POSCat, more specifically POSCat.Description. It then
sums these categories as they appear in the sales history table
InvLine. What I like about this layout is that even if there is a null
sum value for a category it still will be listed and I can use
something like iif to make it $0.00.
Select POSCat.*,(Select Sum(InvLine.Price) From InvLine Where
POSCat.Description = InvLine.Cat) as Totals
FROM POSCat
The recordset looks like this
cat1 sumcat1
cat2 sumcat2
Now although that works on some levels its very limited and only gives
me a total for the table. What I really need is to verify that the
item has been paid for by comparing the invoice total to invoice paid,
also be able to search by date. What I came up with is this:
SELECT InvLine.Cat, SUM(InvLine.Price) AS Total
FROM Payments
INNER JOIN ((InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId)
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc
The recordset looks like this if there was values only in those
categories 1, 4, 5
cat1 sumcat1
cat4 sumcat4
cat5 sumcat5
Which works great except it only lists the categories which are not
null but I would like to list all of them like the first query does.
After a little help I realized that I needed to do an Outer Join
I tried this a few diffent ways but Access is complaining that the join
is not supported, so my SQL must be setup wrong. Here is what I have
so far, again any help is appreciated.
SELECT POSCat.Description, Sum(Payments.Value) AS Total
FROM POSCat LEFT OUTER JOIN (Payments INNER JOIN ((InvLine INNER JOIN
Invoice ON InvLine.IId = Invoice.IId) INNER JOIN PaySplit ON
Invoice.IId = PaySplit.IId) ON Payments.PAYId = PaySplit.PayId) ON
POSCat.Description = InvLine.Cat
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate Between
#3/20/2006# And #3/22/2006#
GROUP BY POSCat.Description
ORDER BY POSCat.Description Asc