Solved

Count records from Access Query

Posted on 2008-06-11
6
1,334 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

761 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