Data Comparison Query

[AndrewKent]

Hi there,

I have a bit of a complex ask here:

I have a table in Access which contains individual records for staff salary as at Week 1, Week 2 ect ect and is stored as such:

Employee_ID Name Salary Week
0123 Andrew 10,000 1
0123 Andrew 10,500 2

...and so on


I need to be able to create a Query that:

1) Contains ALL staff as at Week 1, with a column for what their salary was in Week 1 and a column for what their salary was in Week 2.

SELECT tblData_Colleagues_Historical.ch_employeeid, tblData_Colleagues_Historical.ch_firstname, tblData_Colleagues_Historical.ch_lastname, tblData_Colleagues_Historical.ch_week, tblData_Colleagues_Historical_1.ch_weekFROM tblData_Colleagues_Historical AS tblData_Colleagues_Historical_1 RIGHT JOIN tblData_Colleagues_Historical ON tblData_Colleagues_Historical_1.ch_employeeid = tblData_Colleagues_Historical.ch_employeeid
WHERE (((tblData_Colleagues_Historical.ch_week)=5) AND ((tblData_Colleagues_Historical_1.ch_week)=9));

The issue is that the staff population is different from one week to the next (as people join/leave) so I want to be able to show ALL staff from Week 1, not just where the join between the two tables is equal. What am I doing wrong?

A

 

[Joe4]

You don't need to Join the table to itself, you can do it in a single Aggregate Query.
Here is an example, based on the data table you posted (I was going to try to do it off of your SQL code, but did not see any Salary field referenced in your code).

SELECT 
    MyTableName.Employee_ID, 
    MyTableName.Name, 
    Sum(IIf([MyTableName]![Week]=1,[MyTableName]![Salary],0)) AS Week_1_Salary, 
    Sum(IIf([MyTableName]![Week]=2,[MyTableName]![Salary],0)) AS Week_2_Salary
FROM 
    MyTableName
GROUP BY 
    MyTableName.Employee_ID, 
    MyTableName.Name
HAVING 
    (Sum(IIf([MyTableName]![Week]=1,[MyTableName]![Salary],0))>0) 
    OR (Sum(IIf([MyTableName]![Week]=2,[MyTableName]![Salary],0))>0);