Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rank Daily Results

Status
Not open for further replies.

MikeAuz1979

Programmer
Aug 28, 2006
80
AU
Hi, Using access 2000 I have a query that produces results like:

Site# Date Usage
1 01-jan-07 300
1 02-jan-07 250
1 03-jan-07 400

Would anyone have any idea How I could add another field that ranks the usage like below?

Site# Date Usage Rank
1 01-jan-07 300 2
1 02-jan-07 250 3
1 03-jan-07 400 1

Thanks for any help
Mike
 
Something like this ?
SELECT A.[Site#], A.Date, A.Usage, Count(*) AS Rank
FROM tbl2Rank AS A INNER JOIN tbl2Rank AS B ON A.[Site#] = B.[Site#] AND A.Usage <= B.Usage
GROUP BY A.[Site#], A.Date, A.Usage
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV That's great, but would you have any idea how I could incorporate this into my current query:
(So I get these fields and your Rank field)

SELECT CCDR_SITE.SITE_NUMBER, CCDR_SITE.SITE_NAME, CCDR_AGL_VOLUMES.READING_DATE, CCDR_AGL_VOLUMES.CONSUMPTION_GJ, QryNewMDQSumHistory.BOOKED_GJ_BASE, [HV_VALUE]/1000 AS HeatingValue FROM QryNewMDQSumHistory RIGHT JOIN (CCDR_SUBCONTRACT RIGHT JOIN ((CCDR_SITE INNER JOIN CCDR_AGL_VOLUMES ON CCDR_SITE.DELIVERY_POINT_ID = CCDR_AGL_VOLUMES.STATION_ID) INNER JOIN CCDR_ACTIVE_SITE ON CCDR_SITE.SITE_NUMBER = CCDR_ACTIVE_SITE.SITE_NUMBER) ON CCDR_SUBCONTRACT.ACCOUNT_NUMBER = CCDR_ACTIVE_SITE.ACCOUNT_NUMBER) ON QryNewMDQSumHistory.SUBCONTRACT_NUMBER = CCDR_SUBCONTRACT.SUBCONTRACT_NUMBER WHERE QryNewMDQSumHistory.BOOKING_START_DATE<=[READING_DATE] AND QryNewMDQSumHistory.[Booking End Date]>=[READING_DATE] AND CCDR_SUBCONTRACT.ACTUAL_CONTRACT_START_DATE<=[READING_DATE] AND CCDR_SUBCONTRACT.SUBCONTRACT_EXPIRY_DATE>=[READING_DATE] AND CCDR_AGL_VOLUMES.READING_DATE >= Date()-1 AND CCDR_AGL_VOLUMES.READING_DATE <= Date()-1 AND CCDR_SITE.SITE_NUMBER = '099989700' order by 1 asc, 2 asc, 3 asc

Thanks Heaps
Mike
 
Hey all, further to this is there a way in access to do the same type of subqueries that you can do in SQL server/Oracle.

For Example:

Select u.Site#, u.Site_Name,u.Date u.Usage, MySubQuery.Rank
from tblUsage u,

(SELECT A.[Site#], A.Date, Count(*) AS Rank
FROM tbl2Rank AS A INNER JOIN tbl2Rank AS B ON A.[Site#] = B.[Site#] AND A.Usage <= B.Usage
GROUP BY A.[Site#], A.Date, A.Usage
ORDER BY 1, 2) MySubQuery

Where MySubQuery.Date = u.Date
AND MySubQuery.Site# = u.Site#

Thanks for any help

 
SELECT u.[Site#], u.Site_Name, u.Date, u.Usage, r.Rank
FROM tblUsage u INNER JOIN (
SELECT A.[Site#], A.Date, Count(*) AS Rank
FROM tbl2Rank AS A INNER JOIN tbl2Rank AS B ON A.[Site#] = B.[Site#] AND A.Usage <= B.Usage
GROUP BY A.[Site#], A.Date
) r ON u.Date = t.Date AND u.[Site#] = r.[Site#]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top