hologosesh
asked on
Count records from Access Query
I currently have an access database with a query that I need to tweak. The existing query is:
SELECT ssUsers.custom1 AS Location, Sum(DateDiff("d",[CheckIn] ,[CheckOut ])*[rooms] ) AS ActualRoomNights, Sum(reservations.revenue) AS TotalRevenue, [TotalRevenue]/[ActualRoom Nights] AS ADR, Sum(DateDiff("d",[effectiv eCheckIn], [effective CheckOut]) *[rooms]) AS EffectiveRoomNights, [ADR]*[EffectiveRoomNights ] AS EffectiveRevenue, Sum(([revenue]/(DateDiff(" d",[CheckI n],[CheckO ut])*[room s]))*(Date Diff("d",[ effectiveC heckIn],[e ffectiveCh eckOut])*[ rooms])) AS EffectiveRevenueReal, ssUsers.Custom3 AS Team, ssUsers.custom2 AS RDO, reservations.ressource
FROM reservations INNER JOIN ssUsers ON reservations.ssUserID = ssUsers.ssUserID
GROUP BY ssUsers.custom1, ssUsers.Custom3, ssUsers.custom2, reservations.ressource;
I call to the query in an asp page like this:
SQL = "SELECT Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team, ressource From vwResults4 WHERE EffectiveRevenueReal <> 0 AND ADR > 19 AND ressource = 0 ORDER By EffectiveRoomNights DESC"
This worked well when I needed to display records by EffectiveRoomNights. However, instead of ordering by EffectiveRoomNights, I now want to sum the number of records where ressource =0 by Location and sort Locations in Descending order. For example, if I have two locations, Location1 and Location2, and Location1 has five different records where ressrouce=0, and Location2 has 4 different records where ressource=0, then I would display Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team, and total records where ressource=0 for Location1 then Location2.
SELECT ssUsers.custom1 AS Location, Sum(DateDiff("d",[CheckIn]
FROM reservations INNER JOIN ssUsers ON reservations.ssUserID = ssUsers.ssUserID
GROUP BY ssUsers.custom1, ssUsers.Custom3, ssUsers.custom2, reservations.ressource;
I call to the query in an asp page like this:
SQL = "SELECT Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team, ressource From vwResults4 WHERE EffectiveRevenueReal <> 0 AND ADR > 19 AND ressource = 0 ORDER By EffectiveRoomNights DESC"
This worked well when I needed to display records by EffectiveRoomNights. However, instead of ordering by EffectiveRoomNights, I now want to sum the number of records where ressource =0 by Location and sort Locations in Descending order. For example, if I have two locations, Location1 and Location2, and Location1 has five different records where ressrouce=0, and Location2 has 4 different records where ressource=0, then I would display Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team, and total records where ressource=0 for Location1 then Location2.
ASKER
The key is Location, then we display the other values for all ressource = 0. Additionally, we display how many records where ressource=0 exist for each Location and sort by that number in descending order.
Thanks!
Thanks!
OK, how are yuo wanting to see this report?
Loc ARN TR ADR ERN ERR TotCount
Loc 1 1 50 1 3 50 7
Loc 2 2 100 3 1 75 3
or
Loc ARN TR ADR ERN ERR TotCount
Loc 1 2 40 5 2 60 4
Loc 1 4 50 3 3 25 2
Loc 2 2 75 3 1 65 2
Loc 1 1 50 1 3 50 1
Loc 2 2 100 3 1 75 1
I guess a better way to ask the question is what grouping do you want for the query?
Shawn
Loc ARN TR ADR ERN ERR TotCount
Loc 1 1 50 1 3 50 7
Loc 2 2 100 3 1 75 3
or
Loc ARN TR ADR ERN ERR TotCount
Loc 1 2 40 5 2 60 4
Loc 1 4 50 3 3 25 2
Loc 2 2 75 3 1 65 2
Loc 1 1 50 1 3 50 1
Loc 2 2 100 3 1 75 1
I guess a better way to ask the question is what grouping do you want for the query?
Shawn
ASKER
The first one, thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This one appear to work as requested. Thank you very much.
Shawn