I need query help

[leevbyrne]

I have a database for uni,
I need to query fname lname and salary from staff which have a salary greater from the averagesalary which is all well and good, I have this done. But we have to include the difference that the staffs salary is from the average, and all attempts of this fail.

 

[plog]

Sample data from your tables, SQL of your query, actual output of the query and expected output of the query.

 

[Privateer]

leevbyrne:
The answer is pretty easy. Create a query where you average the salaries from the source table using the following formula:
AvgComp: Avg(tblCompensation.Salary)

Create another query with the source compensation table and this average query as recordsources. There is no relationship, so don't draw a line. The three fields will be

tblCompensation.Salary
qryAverageCompensation.AvgComp
CompDiff: tblCompensation.Salary-qryAverageCompensation.AvgComp

Because there is no relationship between the sources, the query acts like a union query and the average shows up in every row, right next to each person's salary, so the math is easy.

Someone showed me this years ago, so I am glad I can pass it on. Hope it helps.
Cheers,
Michael