Solved

Count records from Access Query

Posted on 2008-06-11
6
1,330 Views
Last Modified: 2012-05-05
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
Comment
Question by:hologosesh
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:scgstuff
ID: 21764052
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
 

Author Comment

by:hologosesh
ID: 21764129
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
 
LVL 5

Expert Comment

by:scgstuff
ID: 21764305
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:hologosesh
ID: 21765447
The first one, thanks.
0
 
LVL 5

Accepted Solution

by:
scgstuff earned 500 total points
ID: 21772989
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
 

Author Closing Comment

by:hologosesh
ID: 31466316
This one appear to work as requested.  Thank you very much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now