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.Descript ion. 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.*,(Selec t Sum(InvLine.Pri ce) From InvLine Where
POSCat.Descript ion = 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.Pri ce) 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.PayDat e 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@hotmail.com]

Re: SQL Logic Help

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]

Re: SQL Logic Help

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.Descript ion = InvLine.Cat


SELECT InvLine.Cat, SUM(InvLine.Pri ce) 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.PayDat e BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc

[tranceport185]

Re: SQL Logic Help

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.Descript ion, Sum(Payments.Va lue) 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.Descript ion = InvLine.Cat
WHERE (((Invoice.Tota l)=[Invoice].[Paid]) AND ((Payments.PayD ate)
Between #3/20/2006# And #3/22/2006#))
GROUP BY POSCat.Descript ion
ORDER BY POSCat.Descript ion Asc

[salad]

Re: SQL Logic Help

tranceport185 wrote:
[color=blue]
> 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.Descript ion, Sum(Payments.Va lue) 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.Descript ion = InvLine.Cat
> WHERE (((Invoice.Tota l)=[Invoice].[Paid]) AND ((Payments.PayD ate)
> Between #3/20/2006# And #3/22/2006#))
> GROUP BY POSCat.Descript ion
> ORDER BY POSCat.Descript ion Asc
>[/color]
What happens if you remove the word Outer?

[tranceport185]

Re: SQL Logic Help

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]

Re: SQL Logic Help

"tranceport 185" <sbatschelet@gm ail.com> wrote in
news:1142990103 .608969.250900@ v46g2000cwv.goo glegroups.com:
[color=blue]
> 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.Descript ion, Sum(Payments.Va lue) 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.Descript ion = InvLine.Cat
> WHERE (((Invoice.Tota l)=[Invoice].[Paid]) AND
> ((Payments.PayD ate) Between #3/20/2006# And #3/22/2006#))
> GROUP BY POSCat.Descript ion
> ORDER BY POSCat.Descript ion Asc
>[/color]

Does this work? (Outer join removed)

SELECT InvLine.Cat,
Sum(Payments.Va lue) 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.Tota l)=[Invoice].[Paid])
AND ((Payments.PayD ate)
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]

Re: SQL Logic Help

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]

Re: SQL Logic Help

tranceport185 wrote:[color=blue]
> 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
>[/color]

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

--
Smartin

[tranceport185]

Re: SQL Logic Help

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.Descript ion , InnerJoinCatego ryReport.Total" _
& "FROM POSCat LEFT JOIN InnerJoinCatego ryReport ON
InnerJoinCatego ryReport.Cat = POSCat.Descript ion "

which references saved query InnerJoinCatego ryReport

SELECT InvLine.Cat, Sum(InvLine.Pri ce) AS Total
FROM Payments INNER JOIN ((InvLine INNER JOIN Invoice ON
InvLine.IId=Inv oice.IId) INNER JOIN PaySplit ON
Invoice.IId=Pay Split.IId) ON Payments.PAYId= PaySplit.PayId
WHERE (((Invoice.Tota l)=Invoice.Paid ) And ((Payments.PayD ate) 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]

Re: SQL Logic Help

"tranceport 185" <sbatschelet@gm ail.com> wrote in
news:1143131762 .586493.195540@ u72g2000cwu.goo glegroups.com:
[color=blue]
> 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.Descript ion ,
> InnerJoinCatego ryReport.Total" _
> & "FROM POSCat LEFT JOIN InnerJoinCatego ryReport ON
> InnerJoinCatego ryReport.Cat = POSCat.Descript ion "
>
> which references saved query InnerJoinCatego ryReport
>
> SELECT InvLine.Cat, Sum(InvLine.Pri ce) AS Total
> FROM Payments INNER JOIN ((InvLine INNER JOIN Invoice ON
> InvLine.IId=Inv oice.IId) INNER JOIN PaySplit ON
> Invoice.IId=Pay Split.IId) ON Payments.PAYId= PaySplit.PayId
> WHERE (((Invoice.Tota l)=Invoice.Paid ) And ((Payments.PayD ate)
> 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
>[/color]
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.