Help on SQL getting a cartesean join

[maxflitom]

Hello Tek-Tips,

I am attempting to summarize customer totals into one amount from two different Access Tables, the Events and EventDetail Tables. The Sum() function from the EventDetail table returns the correct result, however, the amounts returned from the Events Table are duplicated by the amount of entries in the EventDetail Table. There is only one record in the Events Table and could be many in the EventDetail table Here is my SQL string:

SELECT Customers.CustomerName, sum(EventDetail.Quantity*EventDetail.Price+Events.MeetingRoomPrice+Events.Grat11+Events.Grat7+Events.SalesTax) AS EventTotal
FROM (Events INNER JOIN Customers ON Customers.CustomerID=Events.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

I have tried many variations and still come up with the same result. When I break it down into two seperate SQL strings or use a Union Join I get the correct totals, however I would like them as one total and not two.

Any help will be greatly appreciated!

Tom (maxflitom)

 

[JeffTullin]

Shouldnt this be in a forum about SQL?
There is nothing here that relates to VB except that you may be using VB to execute it.

 

[Golom]

You need a bit of restructuring because the contributions from the "Events" table are being duplicated for each record that you retrieve from the EventDetails table. You should be getting the correct total if there is only one EventDetail.
[tt]
SELECT C.CustomerName,

((Select SUM(D.Quantity*D.Price) From EventDetail D
Where D.EventID = E.EventID) +
Sum(E.MeetingRoomPrice)+
Sum(E.Grat11)+
Sum(E.Grat7)+
Sum(E.SalesTax)) AS EventTotal

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

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

GROUP BY C.CustomerName
[/tt]
All the "Sum" clauses are used so that we don't have to Group By those fields. If you are certain that the relationship between "Customers" and "Events" is one-to-one then you can eliminate the SUM operations on the Events fields and repeat those fields in the Group By clause.

 

[maxflitom]

JeffTullin,

Sorry I should have been let you know what I needed it for. I am using VB6 and wish to generate a DataReport using the Data Report Designer.

Didn't want to make my question longer than it should have been,

Tom (maxflitom)

 

[maxflitom]

Thanks Golom, I will try it. I will send you a response and a checkmark when I get it to work.

Tom (maxflitom)

 

[johnwm]

It's a whole lot easier now we know it's a DataReport!

In your DataReport Footer section, right click, Insert Control | Function. In the Properties window select Function type as
rptFuncSum and set the DataField to whatever you want the total for.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[maxflitom]

johnwm,

I have already attempted that route. All I want my report to accomplish is a list of customers and the total spent within a date range. If I can accomplish this in one SQL string, it seems that it would be the most efficient.

Tom (maxflitom)

 

[maxflitom]

golom,

We are almost there. The only issues I have are in your SQL, I had to add the Events.EventID to the Group By. When I do this, instead of getting just one total for each customer, I get the same number as there are items in the EventDetail Table. I tried some variations and still cannot accomplish one grand total for each customer.

Tom (maxflitom)

 

[Golom]

Why did you have to add it?

If you want, as you say "... just one total for each customer ...", adding "EventID" to the Group By will have the effect of giving you one record for each event for each customer.

Can we see your revised SQL? The SQL that I gave you involves the EventDetail table only in a correlated subquery. You should no longer be doing a join to that table in the main query ... hence the number of records generated in the main query should have nothing to do with the EventDetail record count.

 

[Golom]

Just curious ... did you get it working?

 

[maxflitom]

golom,

Sorry for the late response. The reason I added the EventID in the Group By clause is I get an error when I run the query that states the the EventID was not include EventID as part of the aggregate expression.

Here is SQL on a Union Select which returns the correct results, however, there are two results and not one.

SELECT Customers.CustomerName, IIf(ISNULL(Sum(EventDetail.Quantity*EventDetail.Price)),0,Sum((EventDetail.Quantity*EventDetail.Price))) AS Total
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 Total
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;

All I added to your SQL is in the Group By clause:
GROUP BY C.CustomerName, E.EventID

Tom (maxflitom)