Combine two SQL queries in a cfquery

I am using MS Access and Coldfusion to create a cfquery that will output a list containing the number of miles that users have accumulated during a current year and list those users and their accumulated miles, in descending order with a ranking number of 1 for the user with the most miles.

tbl_mileusers has 2 fields: userkey and username.
tbl_miles has 4 fields: userkey, milesdate, and miles.

Using Access' Query Design mode, I have created two queries:
qry 1 reads:
SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username;

qry 2 reads:
SELECT myqry2.username, myqry2.SumOfmiles, (Select Count(*) from myqry1 Where [Sumofmiles]>[myqry2].[SumOfmiles])+1 AS Ranking
FROM myqry1 AS myqry2
ORDER BY myqry2.SumOfmiles DESC;

Running qry2 in Access produces exactly the output that I want. However, qry2 requires using qry1 in its SQL statement and I do not know the proper syntax to combine the 2 queries into 1 SQL statement which I need to construct my <cfquery>. (At least I assume that I need only 1 SQL statement to create a <cfquery> because I do not know any other way to create a cfquery).

Might someone provide me with the correct SQL to use to create my cfquery?

I am a beginner at this so if I am missing something obvious I appreciate your patience.
Thanks
Lee

Start Free Trial