Solved

Count records from Access Query

Posted on 2008-06-11
6
1,335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 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