Combining two SELECT SQL MS Access queries into a single query to make a table with two columns

I have two queries in SQL for MS Access. First one is:

SELECT AVG(Salary) AS AverageSalary FROM Table WHERE EducationLevel = 1;

and second query:

SELECT AVG(Salary) AS AverageSalary2 FROM Table WHERE EducationLevel = 2;

How can I combine them into one query to produce a table with two columns, labelled "AverageSalary" and "AverageSalary2"?

Solution 1:

Can accomplish in one query. Consider:

Conditional expressions:

SELECT Avg(IIf(EducationLevel = 1, Salary, Null)) AS AverageSalary, 
       Avg(IIf(EducationLevel = 2, Salary, Null)) AS AverageSalary2
FROM [Table];

Or a CROSSTAB:

TRANSFORM Avg(Table.Salary) AS AvgOfSalary
SELECT 1 AS R
FROM [Table]
GROUP BY 1
PIVOT "AverageSalary" & [EducationLevel];

Or build a report and use its Sorting & Grouping features with aggregate calculations. This easily allows display of detail records as well as summary data.