SQL Logic Help

[tranceport185]

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.

I am looking for help on how to merge the 2 queries together so that I
can list all categories but also search by date and make sure
Invoice.Total = Invoice.Paid

THanks in advance

[pietlinden]

you can outer join the categories to the query in question so that all
the categories show up, regardless of whether they have related records
in your other query. Then you can format the null as zero using
NZ([SomeField])

[tranceport185]

Right ok I got a little bit of tunnel vision using the Inner Join, I am
kind of a newbie to this so how would you add the outer join to this
query. I'm sorry to be a pain but could you show me givin this query
how I could use a line like this, assuming that my example is what you
meant. See my problem is I need FROM Payments on my Inner Join then
POSCat on my outer how can I do both?
FROM POSCat LEFT OUTER JOIN ON POSCat.Description = InvLine.Cat
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

[tranceport185]

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

[salad]

tranceport185 wrote:

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

What happens if you remove the word Outer?

[tranceport185]

Hi actually didn't try that but it gave me the same error its not that
it doesn't support the LEFT OUTER JOIN (well from what I have read at
least) its that it doesn't like the way I did it. But thanks for the
post.

[Bob Quintal]

"tranceport185" <sb*********@gmail.com> wrote in
news:11**********************@v46g2000cwv.googlegr oups.com:

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

Does this work? (Outer join removed)

SELECT InvLine.Cat,
Sum(Payments.Value) AS Total
FROM (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)
WHERE (((Invoice.Total)=[Invoice].[Paid])
AND ((Payments.PayDate)
Between #3/20/2006# And #3/22/2006#))
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc

If it does, save that as a query and do the outer join in a
second query that calls the first. Access sometimes gets
confused with mixed join types.

--
Bob Quintal

PA is y I've altered my email address.

[tranceport185]

This is going to get interesting see my problem is the DB is part of a
closed system I cannot add or remove tables, queries, but I do have
permmisions to view/add/edit data. My hopes were to solve this using
pure SQL. I wonder if I could create a linked table in a second DB
with all of the info then create a query using linked tables, then
alter the SQL to use the linked tables ouch thats ugly. Any other ways
around this guys/gals?

PS that SQL is correct and thanks for the post

-Sam

[Smartin]

tranceport185 wrote:

This is going to get interesting see my problem is the DB is part of a
closed system I cannot add or remove tables, queries, but I do have
permmisions to view/add/edit data. My hopes were to solve this using
pure SQL. I wonder if I could create a linked table in a second DB
with all of the info then create a query using linked tables, then
alter the SQL to use the linked tables ouch thats ugly. Any other ways
around this guys/gals?

PS that SQL is correct and thanks for the post

-Sam

I would try the linked table approach. The SQL works no differently.

--
Smartin

[tranceport185]

Yes your correct and everything seems to be working except I can't get
the date variables to pass from ASP

I have

strSQL = "SELECT POSCat.Description , InnerJoinCategoryReport.Total"_
& "FROM POSCat LEFT JOIN InnerJoinCategoryReport ON
InnerJoinCategoryReport.Cat = POSCat.Description "

which references saved query InnerJoinCategoryReport

SELECT InvLine.Cat, Sum(InvLine.Price) AS Total
FROM 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
WHERE (((Invoice.Total)=Invoice.Paid) And ((Payments.PayDate) Between
[dtStartDate] And [dtEndDate] ))
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat;

If I run this in access it asks for the dates as it is setup
parameterized and will run. But how can I write the ASP because my
paramerized query is a sub query if this makes any sence!?! Any help
woul be great

[Bob Quintal]

"tranceport185" <sb*********@gmail.com> wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

Yes your correct and everything seems to be working except I
can't get the date variables to pass from ASP

I have

strSQL = "SELECT POSCat.Description ,
InnerJoinCategoryReport.Total"_
& "FROM POSCat LEFT JOIN InnerJoinCategoryReport ON
InnerJoinCategoryReport.Cat = POSCat.Description "

which references saved query InnerJoinCategoryReport

SELECT InvLine.Cat, Sum(InvLine.Price) AS Total
FROM 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
WHERE (((Invoice.Total)=Invoice.Paid) And ((Payments.PayDate)
Between [dtStartDate] And [dtEndDate] ))
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat;

If I run this in access it asks for the dates as it is setup
parameterized and will run. But how can I write the ASP
because my paramerized query is a sub query if this makes any
sence!?! Any help woul be great

not sure what you are asking, but one trick I've done is to
create parameters in the parent query, then refer the sub-query
to those parameters. you may need to set up calculated fields
in the parent query to receive the parameters.

--
Bob Quintal

PA is y I've altered my email address.