Date Dependant Query 1

[TechAnalyst100]

I have a table with a set of names (Staff), their start date with company, their original working hours, plus other info. I have another table (Shift_Change) which allows you to choose a name and enter their new working hours, and the date they are effective from.

As a person can in theory, change their working hours on numerous occasions,I would like to set up a query which will produce a list of all staff and their current working hours to a given date.

Could anyone give me any pointers in writing a query to utilise both tables and provide me with this list? Thanks.

 

[M8KWR]

you you basically want a query to show you the most recent date change, for all staff, showing there present working number of hours?

1 record per staff member?

 

[TechAnalyst100]

All of your assumptions are correct. However, I may want to look for staff and their hours a year ago, and so would be querying the most recent date change at that point in time.

 

[M8KWR]

Now you are asking for a query and a half...

I am unsure if that one can be done.

How often could a staff member change there hour rate. Is there a set procedure in place, or could it be change lets say twice in one month.

 

[PHV]

Can you please post the structure of the 2 tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TechAnalyst100]

Table:Staff Table:Transfer
Field:Staff_ID Field:Transfer_ID
Field:Staff_Name Field:Staff_ID
Fieldate_Started Field:New_Hours
Field:Starting_Hours Fieldate_Effective_From
Field:Status

Staff.Staff_ID is linked in a one to many relationship with Transfer.Staff_ID.

Staff.Date_Started and Transfer.Date_Effective_From are the fields which would need to supply a value which is as near to but less than the supplied search date.

 

[TechAnalyst100]

I need to clarify that my most recent post was in response to PHV's request for the structure of two tables.

In response to M8KWR there is no limit to the number of times somebody could change their hours, although it unlikely to happen on a weekly basis, and probably not every month either. However, if there is a solution to this problem I would probably apply it to other date-dependant scenarios where the frequency of change may be higher.

I know this is not a straight-forward request but appreciate any ideas that are forthcoming. Thanks again.

 

[PHV]

You may try this (typed, untested):
SELECT S.*, T.*
FROM Staff S INNER JOIN Transfer T ON S.Staff_ID = T.Staff_ID
WHERE S.Date_Started < [supplied search date]
AND T.Date_Effective_From = (SELECT Max(Date_Effective_From)
FROM Transfer M WHERE M.Staff_ID = S.Staff_ID
AND Date_Effective_From < [supplied search date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TechAnalyst100]

PHV, your SQL code is a step in the right direction, but it only returns a list of people that have had a change in hours.

I need to return a list of all staff and their current hours, so for the staff that have not changed hours they still need to be listed, but their starting hours will be their current hours.

 

[PHV]

Replace INNER JOIN by LEFT JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TechAnalyst100]

Sorry, I get the same results using LEFT JOIN.

However one thing I've noticed is that when I first used your code ACCESS inserts AS twice into the second line so that it looks as follows:

FROM Staff AS S INNER JOIN Transfer AS T ON S.Staff_ID=T.Staff_ID

I don't know if that affects the functioning of the query, but I cannot find any way to prevent this from happening.

 

[traingamer1]

Try this. I needed three queries to return the current hours you are looking for. I leave it to you to add the date criteria to get past hours. (Just use same parameter on Date_started in third query and on Date_effective_from in 1st query)

qSubTransfer - returns the latest date from transfer table

SELECT Transfer.Staff_Id, Max(Transfer.Date_Effective_From) AS MaxOfDate_Effective_From
FROM Transfer
GROUP BY Transfer.Staff_Id;

qsubTran2 - returns latest hours and date

SELECT Transfer.Staff_Id, Transfer.New_Hours, Transfer.Date_Effective_From
FROM Transfer INNER JOIN qSubTransfer ON (Transfer.Date_Effective_From=qSubTransfer.MaxOfDate_Effective_From) AND (Transfer.Staff_Id=qSubTransfer.Staff_Id);

qHours - returns hours from joined tables and uses iif to determine which to use (HoursToUse):

SELECT Staff.staffid, Staff.Staff_name, Staff.Date_started, Staff.Starting_hours, qSubTran2.New_Hours, qSubTran2.Date_Effective_From, IIf(IsNull([New_Hours]),[Starting_hours],[New_Hours]) AS HoursToUse
FROM Staff LEFT JOIN qSubTran2 ON Staff.staffid = qSubTran2.Staff_Id;