Improve company productivity with a Business Account.Sign Up

x
?
Solved

Count records from Access Query

Posted on 2008-06-11
6
Medium Priority
?
1,346 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

Author Comment

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

Accepted Solution

by:
scgstuff earned 2000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

606 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