Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1344
  • Last Modified:

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]/[ActualRoomNights] AS ADR, Sum(DateDiff("d",[effectiveCheckIn],[effectiveCheckOut])*[rooms]) AS EffectiveRoomNights, [ADR]*[EffectiveRoomNights] AS EffectiveRevenue, Sum(([revenue]/(DateDiff("d",[CheckIn],[CheckOut])*[rooms]))*(DateDiff("d",[effectiveCheckIn],[effectiveCheckOut])*[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.  
0
hologosesh
Asked:
hologosesh
  • 3
  • 3
1 Solution
 
scgstuffCommented:
Are the fields Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team the same for each one, or do they differ?  If they differ, which field would stay the same?  Location?

Shawn
0
 
hologoseshAuthor Commented:
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!
0
 
scgstuffCommented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hologoseshAuthor Commented:
The first one, thanks.
0
 
scgstuffCommented:
Try:

SELECT Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team, count(*)
From vwResults4
WHERE EffectiveRevenueReal <> 0 AND ADR > 19 AND ressource = 0
GROUP BY Location, ActualRoomNights, TotalRevenue, ADR, EffectiveRoomNights, EffectiveRevenueReal, Team
ORDER By EffectiveRoomNights DESC

Shawn
0
 
hologoseshAuthor Commented:
This one appear to work as requested.  Thank you very much.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now