SQL getting a cartesean Join 1

[maxflitom]

Hello Tek-Tips,

I have struggled far too long on my attempt to summarize data in my Access Database. I wish to code a SQL statement that will total all of my customer's revenues and group them by CustomerName. Here is the structure of my database:

Events Table: (one Event to many EventDetail items)
MeetingRoomPrice + Grat7 + Grat11 + SalesTax

EventDetail Table:
Quantity * Price

Output:
Customer1 12,100.25
Customer2 8,412.35

Here is a sample of a Union Select statement which returns the correct results, however on two seperate lines per customer.

SELECT Customers.CustomerName, IIf(ISNULL(Sum(EventDetail.Quantity*EventDetail.Price)),0,Sum((EventDetail.Quantity*EventDetail.Price))) AS CustomerTotal
FROM (Customers INNER JOIN Events ON Events.CustomerID=Customers.CustomerID) LEFT JOIN EventDetail ON EventDetail.EventID=Events.EventID
WHERE Events.EventDate Between #1/1/2001# And #12/31/2004# And Events.Canceled=0
GROUP BY Customers.CustomerName
ORDER BY Customers.CustomerName
UNION SELECT Customers.CustomerName, sum(Events.MeetingRoomPrice+Events.Grat11+Events.Grat7+Events.SalesTax) AS CustomerTotal
FROM Events INNER JOIN Customers ON Customers.CustomerID=Events.CustomerID
WHERE Events.EventDate Between #1/1/2001# And #12/31/2004# And Events.Canceled=0
GROUP BY Customers.CustomerName
ORDER BY Customers.CustomerName;

I have tried many variations of joins, and the sum of the Events table is duplicated by the amount of entries in the EventDetail table.

Any help is greatly appreciated.

Tom (maxflitom)

 

[Golom]

Just re-arranging your queries should work
[tt]
SELECT C.CustomerName,

(NZ(Sum(D.Quantity*D.Price)) +

(SELECT SUM(E.MeetingRoomPrice+E.Grat11+E.Grat7+E.SalesTax)

FROM Events E INNER JOIN Customers X
ON X.CustomerID=E.CustomerID

WHERE X.CustomerID = C.CustomerID
AND E.EventDate Between
#1/1/2001# And #12/31/2004#
AND E.Canceled=0)) AS CustomerTotal

FROM (Customers C INNER JOIN Events E
ON E.CustomerID=C.CustomerID)
LEFT JOIN EventDetail D ON D.EventID=E.EventID

WHERE E.EventDate Between #1/1/2001# And #12/31/2004#
And E.Canceled=0

GROUP BY C.CustomerName
[/tt]

 

[Rolliee]

Tom,

I find it works well if I do a query in the query mode and let the wiz help me. Then when I get it just like I want it, I do a VIEW/ SQL and cut and past and use that as the data source for my form/ report/ whatever.

rollie@bwsys.net

 

[BNPMike]

To answer your question you do a Cartesian join like:

Select Town, Population, Star, Planet
from City, SolarSystem

This will give you all combinations of planets and towns. Almost certainly this isn't what you want but I mention it as you thought you did. Note there is no join between the tables so SQL just links everything to everything.

 

[Rolliee]

RE: cartesian join. Sure you can!!! But what have you when you have such a 'join?' Gobbledygook! How do you change/ use anything?

 

[maxflitom]

Golom,

Thanks for the post. I have been a little out of it for a few days which is why it has taken me so long to reply. I will let you know how it works.

Thanks Again!!!

Tom (maxflitom)