Solved Monthly Wage Check

[Kayleigh]

Hi
Another query I am trying to build is to check if a wage has been calculated for each staff member within the last month and also to check if they have been employed over a month ago. How is the best way to build this?

 

[June7]

The first sounds like a Find Unmatched query.

The second would be a fairly simple SELECT using a date field in WHERE clause. Depends what you want to consider a "month".
SELECT * FROM table WHERE HireDate < Date() - 30;

 

[arnelgp]

another answer:

1. you will never know until you saved each wage calculation in a table:

tblPayOut (table)
WageID (autonumber)
StaffID (number, long) 'FK to Staff table
Month (integer) ' month number
Year (integer) ' the year
Wage (currency or double or decimal)

then using Unmatched query as proposed, to know which have Staff member who's wage is not in tblPayOut:

select tblStaff.StaffID, tblStaff.StaffName From tblStaff Left Join tblPayOut
on tblStaff.StaffID = tblPayOut.StaffID
where tblPayOut.StaffID Is Null And tblPayOut.Month = theMonth And tblPayOut.Year =theYear;

 

[ebs17]

How is the best way to build this?

A query would be based on tables. Do you have tables? Which? Relationship picture?

 

[Kayleigh]

Thanks again for the tips above. It was pretty straightforward to iterate through each staff member and compare the dates of start and last paid - using your sample queries above!

And of course half the solution is knowing the question - which is why I find it so useful to air my questions on this forum. It just clarifies for me the problem and helps build a solution.

 

[gemma-the-husky]

As @arnelgp says, the wages paid ought to be in a separate table.

storing the last paid date in the employee record won't save much time, if any, and could lead to inconsistent updates.