CODEWITHSUNDEEP
ms-access
Lio
Lio

Reputation: 3

join two records from the same table

(This is in access) I have a table with all the history of the wages of my workers. Each record has an starting date in which the employee started receiving that wage and a starting date in which we started reporting this wage. Why?, legal stuff.. (Sometimes the starting date is older than the date we started reporting it)

-------------------------------------------------------
|WorkerID|StartingDate|ReportingDate|Salary|
-------------------------------------------------------
|   001  | 01/01/2015 |  01/01/2015 |10,000|
|   001  | 01/01/2016 |  01/02/2016 |15,000|
-------------------------------------------------------

So if I want to make a check for the worker 001 on 01/01/2016 it should be $15,000, but i have to report $10,000. So now I need a query that tells me the wage I should pay and the wage I should Report, just like this:

------------------------------------------------------------
|WorkerID|StartingDate|ReportingDate|PaySalary|ReportSalary|
------------------------------------------------------------
|   001  | 01/01/2016 |  01/02/2016 |  15,000 |   10,000   |
------------------------------------------------------------

The table is called Wages_History and I don´t have a clue of how to start the query... Thanks!

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If I understand correctly, this is a simple where filter:

select *
from wages_history
where reporting_date > starting_date;

If this is the case, then I would suggest that you devote some effort to learning SQL . . . there are good books, tutorials, and courses available.

EDIT:

If you also want the previous salary:

select wh.*,
       (select top 1 wh2.salary
        from wages_history wh2
        where wh2.worker_id = wh.worker_id and
              wh2.starting_date <  wh.starting_date
        order by wh2.starting_date desc
       ) as prev_salary
from wages_history wh
where reporting_date > starting_date;

Upvotes: 1

Related Questions