changing values without altering previous data (1 Viewer)

[Shoutaro]

Good day to everyone

I have a data base for managing employee’s attendance and work hours, vacation leave etc... it started a very simple application keeping records of attendance in time it complicated itself quite a bit. One major issue that I’ve been struggling around is when one of the employee changes the roster during the year (not 1st Jan) because everything messes up :banghead: i.e. a new roster has different working hours and different break hours.

Usually I have a query that joins the employee with his current roster by means of <[StaffID] and [RosterYear]> but when they change roster in mid-year I have two entries for that year! duplicating all entries. Is there a way to have the entries before the change not altered and the entries after with the updated roster data?

Thanks

 

[Uncle Gizmo]

(not 1st Jan)

What does that mean?

 

[Uncle Gizmo]

You need to give better descriptions of some and probably all of the following.

employee, attendance, hours, vacation, leave, roster, working hours, break hours,

You should show how they relate together. They may be in the same table, a table might be related to another table.

If your employee/roster association is not working in a manner that suits you, it might be that it needs swapping around.


A lot of the time people begin a database project by building tables and forms with very little idea of where they are going with it. Some people object to this method, citing that everything should be documented, written out and explained properly before you start. I have to disagree, I think there is a great benefit to be derived by sitting down and creating the tables and forms as you go.

However the people that frown on this method are not stupid, they are correct really, you should have a good idea of where you are going to go with it.

So where am I going with my confusingly worded argument! Well, both have merits, and both have difficulties.

So take a step back and map out what you've done, you will start to see why you made the decisions you did, and mapping it out on a piece of paper like a venn diagram for instance, have a circle for employee, another for attendance, another for hours etc, then draw lines linking them together. Write in what links them and why you think it should be linked like that.

Your actual workflow process will help you with this, it will show you which items are important, which item should be done first and what effect they all have on each other.

So what I am saying is, I get the impression that your database has evolved to where it is, and now it's time to take a step back, deconstruct it as it were and then put it back together in such a way that it can function as you want it to.

 

[CJ_London]

I agree with Unc. Sounds like you need an extra table to link employee to roster with from/to dates

tblEmployees
employeePK
employeeName
…
….

tblRosters
RosterPK
RosterName
…
…

tblLinkEmpRosters
EmpRosterPK
employeeFK
RosterFK
FromDate
ToDate

 

[Shoutaro]

Thanks for your reply

You are right I started this project to replace an excel sheet that did all the stuff but was terribly slow and crashed several times. Access was my obvious way forward (also because it was available at my work ;-)) As you perfectly said I build the tables, forms, queries and code that suited the needs for that period and every think was fine. But as time passed new personnel joined and left with their new roster, and other HR stuff that will take quite some time to explain “correctly”. The problem I have some original sins in my design that I don’t know how to correct with compromising the data. I was already planning in my mind to start a fresh database now that I have better knowledge of my needs but this cannot happen until next year.
The attached image shows the tables related to my problem (there are other that deal with daily attendance, oncall hours etc.…. not shown for simplicity)
The focal table is [tblStaffInfo] and here is where I’ve made the original sin (highlighted in yellow) I have other tables for Grade and Section but they have no influence in this regard.

[tblRosterCodes] is the table that stored the different rosters available in my place of work, BreakD is the decimal representation of the break time (e.g. 30min = 0.5) VL is a number representing the hours a vacation leave costs.

[tblStaffRosterChage] is my attempt to fix things

[tblEvents] stores for each employee whether he availed of Vacation Leave, Sick Leave, Overtime and 10 other different events.

[tblVacationLeaveDonated] is another headache that was introduce later (but with a bit of luck I can eliminated and integrate its data (very few) in [tblEvent] with the addition of an extra field.

What I had in mind to fix thing before I go for a fresh start is to have a query that compares the dates < [tblStaffRosterChage] .[YearOfChange] with [tblEvents].[EvetDate] and accordingly choose the correct [CostedCode]. I was trying to have a CheckDate field with a iif statement for the above but got stacked.

 

[CJ_London]

you don't need RosterID in tblStaffInfo - you already have it linked via tblStaffRosterChange

In tblStaffRosterChange, you need to remove the YearofChange Field and replace with a DateofChange. Then to find the current Roster, you find the record with the latest DateofChange.

To find the roster on any given day, find the one with the latest DateofChange prior to the date in question e.g. for all employees

SELECT StaffID, RosterCode, Max(DateofChange) as latest
FROM tblStaffRosterChange
WHERE DateofChange<=[Enter Date]
GROUP BY StaffID, RosterCode

you can replace the [Enter Date] parameter with one from a form for example.

 

[Shoutaro]

thanks I'll give it a try and let you Know

thanks

 

[Shoutaro]

Dear CJ_London

I followed your advise I got an improvement for sure! but what I actually need is a list of events for a particular employee not for a single event at a time, so i tried to work around on your principle but it worked 50% of it.

SELECT tblStaffInfo.StaffID, [LastName] & " " & [FirstName] AS StaffName, tblEvents.EventDate, tblStaffRosterChange.RosterCode, Max(tblStaffRosterChange.YearOfChange) AS Latest

FROM (tblStaffInfo LEFT JOIN tblStaffRosterChange ON tblStaffInfo.StaffID = tblStaffRosterChange.StaffID) LEFT JOIN tblEvents ON tblStaffInfo.StaffID = tblEvents.StaffID

WHERE (((tblStaffRosterChange.[YearOfChange])<=[EventDate]))
GROUP BY tblStaffInfo.StaffID, [LastName] & " " & [FirstName], tblEvents.EventDate, tblStaffRosterChange.RosterCode, tblEvents.EventDesID, tblStaffInfo.InService

HAVING (((tblStaffInfo.StaffID)="XXXXX") AND ((tblEvents.EventDate) Between #1/1/2018# And #12/31/2018#) AND ((tblEvents.EventDesID) In (1,2,3,4)) AND ((tblStaffInfo.InService)=Yes))
ORDER BY [LastName] & " " & [FirstName], tblEvents.EventDate;

the query above produced the list i needed but after the date that happened the change in roster I got double entries (i.e. One for the new roster code and another for the old one for that EventDate)

I tried to work around it with an IF statement in the sort of:
CheckRoster: IIF([YearOfChange]<=[EventDate],Max(YearOfChange),Min(YearOfChange))
Expression

this made things worst quadruple entries

any suggestions please

 

[CJ_London]

if you had a roster change during the year I would expect more that one entry so not sure why you think it is an issue, particularly as you are grouping on EventDate and RosterCode. Perhaps you don't need them?

Also not sure why you still have the year of change field.

Suggest you take a look at your table and think about the implications of grouping

 

[Shoutaro]

HI

Thanks for the reply I really appreciated, sorry for the delay from one reply to another but I'm busy on other tasks and I'm out of office most of the time. Please take a look at the attacked snapshot of the query I'm having trouble with. I got to this point using your suggestion (i.e. putting in the criteria <=[EventDate]) but for the Events occurring after date of roster change I'm getting double entries.

I need some sort of logic statement the like of: if [EventDate] <= date of roster change in Roster use this [RosterCode] else use the previous code. The problem is that i'm not skilled enough to figure out how to obtain this result :banghead::banghead:

I hope that i've been clear enough in my wording.

Thanks