Combining two MS Access queries into one result

Question

I have a MS Access table of students and tutors. Each student can have up to one tutor. Tutors themselves may be students, so they may also have a tutor. Example data looks like this:

Student | Tutor
--------|--------
Alice   | 
Brandon | 
Charlie | Alice
Doug    | Charlie
Erin    | Charlie

I want to divide the number of people who are either students or tutors by the number of students. Assume for simplicity that all names here are unique.

I am able to get the query for the numerator:

SELECT Count(*) AS Expr1
FROM (SELECT Student FROM Tutors WHERE Tutor IS NOT NULL
UNION SELECT Tutor FROM Tutors WHERE Tutor IS NOT NULL)

And the denominator:

SELECT Count(*) AS Expr2
FROM (SELECT DISTINCT Student FROM Tutors)

The first query gives me 4, and the second gives me 5. This is good. What I can't figure out how to do without getting errors is combine the two to give me the desired result of 4 / 5 * 100. I've looked at several other solutions, but they seem to have the same table in the FROM statements.

Answer 1

Try this and see if it helps.

SELECT (Numerator.Expr1 / Denominator.Expr2) * 100 AS Percentage
FROM
(
    SELECT COUNT(*) AS Expr1
    FROM (
        SELECT Student FROM YourTable WHERE Tutor IS NOT NULL
        UNION
        SELECT Tutor FROM YourTable WHERE Tutor IS NOT NULL
    ) AS Subquery1
) AS Numerator,
(
    SELECT COUNT(*) AS Expr2
    FROM (SELECT DISTINCT Student FROM YourTable)
) AS Denominator;

Replace YourTable with the actual name of your table. This query calculates the numerator and denominator in subqueries and then combines them in the main query to calculate the percentage.

In this query:

Subquery1 calculates the count of distinct students who are either students or tutors. The denominator calculates the count of distinct students in your table. The main query divides the numerator by the denominator and multiplies it by 100 to get the percentage. This should give you the desired result of 4/5 * 100, which is 80.