SQL Help Summarizing Totals in Table 2

[maxflitom]

Hello Tek-Tips,

My question today is I wish to extract info from a Table (Access 2002) called (RevenueDetails) which includes the Fields (Rooms) and (Revenue). I wish to display today's Rooms & Revenue and month to date Rooms & Revenue in one statement. I am having some difficulty doing this can anyone help?

Example Output:
Today MTD
(Rooms) (Revenue) (MTDRooms) (MTDRevenue)
5 100.25 100 1000.25

I am not sure how to use the SUM(Revenue) & SUM(Rooms) on the same line as my select statement and have the WHERE criteria different?

SELECT Rooms, Revenue FROM RevenueDetails WHERE RevenueDate = #4/20/04#

SELECT SUM(Rooms) AS MTDRooms, SUM(Revenue) AS MTDRevenue FROM RevenueDetails WHERE RevenueDate BETWEEN #4/01/04# AND #4/30/04#

Any suggestions are greatly appreciated.

Tom (maxflitom)

 

[rac2]

I am not sure how to use the SUM(Revenue) & SUM(Rooms) on the same line as my select statement and have the WHERE criteria different?"

Well, you cant.

A lotta times a detail table will have one row for each transaction. If it were a motel a transaction might be one room sold for one night. This might be in a table named RevenueDetails. The total for the date might be

SELECT COUNT(*) AS Rooms,
       SUM(Revenue) AS Revenue
FROM RevenueDetails
WHERE RevenueDate = #4/20/04#

The grand total for the period of time would be

SELECT 
       COUNT(*) AS Rooms,
       SUM(Revenue) AS Revenue
FROM RevenueDetails
WHERE RevenueDate BETWEEN #4/20/04# AND #4/30/04#

You could save these two queries. Lets call them RevenueToday and RevenuePastTenDays. You could display the data from them this way.

SELECT a.Rooms AS TodayCount,
       a.Revenue AS TodayRevenue,
       b.Rooms AS TenDayCount,
       b.Revenue AS TenDayRevenue
FROM RevenueToday a, 
     RevenuePastTenDays b

 

[dhookom]

You can do this all in one sql statement:

SELECT Sum(Abs(RevenueDate=Date())) AS RoomsToday,
  SUM(Abs(RevenueDate=Date()) * Revenue) AS RevenueToday,
  Sum(Abs(Format(RevenueDate,"mm"=Format(Date(),"mm")) AS RoomsMTD,
  SUM(Abs(Format(RevenueDate,"mm")=Format(Date(),"mm")) * Revenue) AS RevenueMTD
FROM RevenueDetails
WHERE Year(RevenueDate) = Year(Date());

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[maxflitom]

Thank you rac2 and dhookom, I will try out both SQL statements and I will post my results when I get it to work.

Sorry it took me so long to respond, I was out of the office.

Thanks Again!

Tom (maxflitom)

 

[maxflitom]

rac2 and khookom,

I tried both statements I get the correct results using rac2's approach, however, I should recieve 9 rows and I get 81, a cartesean join. dhookom, I see where your code is going, however I must sum the Revenue and Rooms fields and I do not see these field names in your SUM() function.

Thanks again for your help!

Tom (maxflitom)

 

[dhookom]

Did you try my solution or are you guessing at the results? It should count the number of records and sum the revenue for the date ranges.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

BTW: Why would you expect 9 rows when your example output only displays 1? Your description seems to suggest a single row.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[maxflitom]

Yes, it should return 9 rows, I simlified the SQL statement for my question. I typed your SQL statement as you have it and it returns 1 as RoomsToday. Here is my statement:

SELECT RevenueDetail.Rooms, RevenueDetail.Revenue, MarketSegments.MarketSegmentID, MarketSegments.MarketSegmentName, MarketSegmentTypes.MarketSegmentType, sum(abs(RevenueDates.RevenueDate=#4/19/2004#)) AS RoomsToday
FROM RevenueDates INNER JOIN (MarketSegmentTypes INNER JOIN (MarketSegments INNER JOIN RevenueDetail ON MarketSegments.MarketSegmentID=RevenueDetail.MarketSegmentID) ON MarketSegmentTypes.MarketSegmentTypeID=MarketSegments.MarketSegmentTypeID) ON RevenueDates.RevenueDateID=RevenueDetail.RevenueDateID
WHERE (((RevenueDates.RevenueDate)=#4/19/2004#))
GROUP BY RevenueDetail.Rooms, RevenueDetail.Revenue, MarketSegments.MarketSegmentID, MarketSegments.MarketSegmentName, MarketSegmentTypes.MarketSegmentType, MarketSegmentTypes.MarketSegmentTypeOrder, MarketSegments.MarketSegmentOrder
ORDER BY MarketSegmentTypes.MarketSegmentTypeOrder, MarketSegments.MarketSegmentOrder;

I shoud return 9 MarketSegments with their details. For Example:

Market Segment Rooms Revenue MTD PTD
Business 2 200 40 800
Leisure 5 1000 100 2500

Tom (maxflitom)

 

[dhookom]

Next time, you shouldn't leave out critical requirements such as "grouping by MarketSegmentName".
Try this query:

SELECT MarketSegments.MarketSegmentID, 
  MarketSegments.MarketSegmentName,
  MarketSegmentTypes.MarketSegmentType,
  sum(abs(RevenueDates.RevenueDate=#4/19/2004#)) AS RoomsToday,
  SUM(Abs(RevenueDate=Date()) * Revenue) AS RevenueToday,
  Sum(Abs(Format(RevenueDate,"mm"=Format(Date(),"mm")) AS RoomsMTD,
  SUM(Abs(Format(RevenueDate,"mm")=Format(Date(),"mm")) * Revenue) AS RevenueMTD
FROM RevenueDates INNER JOIN 
  (MarketSegmentTypes INNER JOIN 
  (MarketSegments INNER JOIN 
   RevenueDetail ON MarketSegments.MarketSegmentID=RevenueDetail.MarketSegmentID) 
  ON MarketSegmentTypes.MarketSegmentTypeID=MarketSegments.MarketSegmentTypeID) ON   RevenueDates.RevenueDateID=RevenueDetail.RevenueDateID
WHERE Year(RevenueDate) = Year(Date())
GROUP BY MarketSegments.MarketSegmentID, MarketSegments.MarketSegmentName, 
MarketSegmentTypes.MarketSegmentType, MarketSegmentTypes.MarketSegmentTypeOrder, 
MarketSegments.MarketSegmentOrder
ORDER BY MarketSegmentTypes.MarketSegmentTypeOrder, MarketSegments.MarketSegmentOrder;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[maxflitom]

dhookom,

Man I really appreciate your help with this. We are almost there. I made some minor modifications to your code, just missed some "(", got some Syntax errors. I fixed them and the MTD total are correct...yea...However the RoomsToday are all 1 and the RevenueToday for all is 0. How can I fix that. I am trying my best to follow your code but still need some clarification.

Tom (maxflitom)

 

[rac2]

Tom,

My solution works only when one row is returned by each query, because 1 x 1 = 1 and 9 x 9 = 81.

It may be that you can modifiy the two queries to GROUP BY MarketSegmentID. That would give you 9 rows in each query. Each row would have a different MarketSegmentID. Then you could JOIN them on that column and obtain no more than 9 rows in the result.

SELECT a.MarketSegmentID,
       a.Rooms AS TodayCount,
       a.Revenue AS TodayRevenue,
       b.Rooms AS TenDayCount,
       b.Revenue AS TenDayRevenue
FROM RevenueToday a 
JOIN RevenuePastTenDays b
  ON a.MarketSegmentID = b.MarketSegmentID

 

[maxflitom]

Duane,

I GOT IT, don't mean to yell but it works and it works well. Just FYI I will post my modified code for reference if anyone else may need help with the same situation.

On November 2, I am voting for dhookom for President!

Thank you so very much, you made my day!!!

My Code:

SELECT MarketSegments.MarketSegmentID, MarketSegments.MarketSegmentName, MarketSegmentTypes.MarketSegmentType, sum(abs(RevenueDates.RevenueDate=#4/19/2004#)*RevenueDetail.Rooms) AS RoomsToday, SUM(Abs(RevenueDate=#4/19/2004#)*RevenueDetail.Revenue) AS RevenueToday, SUM(Abs(RevenueDates.RevenueDate Between #4/3/2004# And #4/19/2004#)*RevenueDetail.Rooms) AS RoomsMTD, SUM(Abs(RevenueDates.RevenueDate Between #4/3/2004# AND #4/19/2004#)*RevenueDetail.Revenue) AS RevenueMTD, FORMAT(RevenueToday/RoomsToday,"currency") AS ADRToday, FORMAT(RevenueMTD/RoomsMTD,"currency") AS ADRMTD
FROM RevenueDates INNER JOIN (MarketSegmentTypes INNER JOIN (MarketSegments INNER JOIN RevenueDetail ON MarketSegments.MarketSegmentID=RevenueDetail.MarketSegmentID) ON MarketSegmentTypes.MarketSegmentTypeID=MarketSegments.MarketSegmentTypeID) ON RevenueDates.RevenueDateID=RevenueDetail.RevenueDateID
WHERE Year(RevenueDate)=Year(Date())
GROUP BY MarketSegments.MarketSegmentID, MarketSegments.MarketSegmentName, MarketSegmentTypes.MarketSegmentType, MarketSegmentTypes.MarketSegmentTypeOrder, MarketSegments.MarketSegmentOrder
ORDER BY MarketSegmentTypes.MarketSegmentTypeOrder, MarketSegments.MarketSegmentOrder;


Tom (maxflitom)

 

[maxflitom]

rac2,

Man, I tried the JOIN thing before messing with Duane's code and could not get it to work. I can follow your statements easier then Duane's and wanted to use your's. I made some modifications to the Queries and you SQL and came up with this which gives me the correct results.


SELECT a.MarketSegmentID, a.MarketSegmentName, a.Rooms AS RoomsToday, a.Revenue AS RevenueToday, b.TotalRooms AS RoomsMTD, b.TotalRevenue AS RevenueMTD, FORMAT(RevenueToday/RoomsToday,"currency") AS ADRToday, FORMAT(RevenueMTD/RoomsMTD,"currency") AS ADRMTD
FROM RevenueToday AS a INNER JOIN RevenueMonth AS b ON a.MarketSegmentID=b.MarketSegmentID
ORDER BY a.TypeOrder, b.SegOrder;

I like breaking up the code like this in multiple SQL statements, this is my first attempt to do so.

rac2, I will also vote for you for President on November 2.

Thank you so much for your help!!!

Tom (maxflitom)