Is this the right table design?

[toast]

Hello all,

I'm hoping for some more pointers on table design as I start to build my database up.
The relevant parts of the database for my current problem area are:

tblSHIFT - a table recording each workshift performed. This table includes the following fields:
ShiftID - primary key
ShiftStartTime
ShiftStopTime

tblCREW - a table recording the 2 employees who work each shift. This table includes the following fields:
CrewID - primary key
ShiftID - foreign key
EmployeeID

Current Issue: What I also need to do is keep track of how much rest each employee got between workshifts.

My thoughts are to add a new field to tblCREW which is a calculated field taking the difference between the previous shift's ShiftStopTime and the current shift's ShiftStartTime (both are fields stored in the parent table tblSHIFT).

The 'ingredients' I'm thinking I need to accomplish this are:
1. Find the last shift worked by that employee.
- create a variable to store the current employeeID
- Create a recordset of tblCREW
- Filter the recordset to the current tblCREW.EmployeeID
- Sort by tblSHIFT.ShiftStopTime (can't sort on tblCREW.ID because they may be added out of chronological order)
- Move to the appropriate first/last record (depending on ASC / DESC) to get that ShiftStopTime
- Store the retrieved previous ShiftStopTime in another variable
2. Do the calculation.
3. Set it to run everytime the tblCREW.EmployeeID changes, or the previous tblSHIFT.ShiftStopTime / current tblSHIFT.ShiftStartTime changes.

I'm not even sure this is the right way to go about it, but if it is I'm not sure how to make this incomplete list of ingredients into the right recipe. I'm very new to Access and VBA, which means I'm sketchy on matters like the use of multiple recordsets (the above references 2 tables) etc.

If anyone can offer any help it will be very much appreciated.

 

[spikepl]

It seems to me you can do all this on the fly using a subquery: check http://allenbrowne.com/subquery-01.html and look at the meter-reading example

 

[PNGBill]

It appears you could combine tblShift and tblCrew.

How are the Shifts organised ?

One Table should be able to hold the data for one shift including the employeeID of each attendant.

As spikepl advises, queries can collect data on who worked on what shift, when and how many hours etc.
Queries can also keep track of rest time.

As long as your Tables have data stored for Shift Start and End Time against EmployeeID then I imagine any relevant data can be obtained as required, by Query.

 

[toast]

Thank you both for the prompt replies.

I've been reading up about subqueries today and they do look like a possibility.
I understood the example where a subquery was used to find the previous record in the same table and I got the idea in the examples where subqueries were used across different tables in the same record.
What I need to do is find the previous record in a different (but linked) table. That is still eluding me. I somehow need to take the current EmployeeID and then find the most recent previous record of that Employee in the table. That I could do if the time was stored in the same table, but it is actually another linked table which stores the time.

Originally I did try to have only one table for this part. It didn't work out because I realised I needed a many-to-many join (each employee being on many shifts, and each shift having many (2) employees). When I tried having one table with 2 extra fields (one for each employee) I ran into problems when I tried to do things like have an employee main form with a subform showing all their shifts.

I'm now wondering if I'm better off having both employees as fields in the workshift table, and base subforms off a query instead.

 

[PNGBill]

Do I detect a little panic grab of ideas going on here.

If I have 150 queries, only 1 is a subquery.
2 or 3 may be Union queries but most are Select Queries or Action Queries which essentialy are select queries that either delete, Append or Update.

For every Table you have there may well be 50 or more queries.

Most of my forms have subforms.
Some have 3 or 4 subforms.

While the above is not a rule what I am trying to show is that Tables are important but it is Queries and Forms that do all the work and of course Reports support these as well.

How do you decide who will be on what shift and what key data do you want to know about a shift ?

From this we may be able to assis you to achieve the end result.

If you try a query or a form or report and can't get it to work, seek help on that issue before giving up as you may find just one change makes it all work or alternativly a new idea is advised on how to approach the issue.

When you create a query, you can select to view it in SQL then copy and past same in the (#) hash keys in the forum post area then reverse the process to get it back to your query design view after some advice has been given.

 

[toast]

Thank you once again for the reply.

The pointers on the usage of queries/subforms/tables has been very useful, and it will certainly help guide me through from now on.

How do you decide who will be on what shift?

This is keeping track of shifts that have happened in the past, rather than forecasting. In that regard, I currently have a one-to-many join to an employee table.

what key data do you want to know about a shift?

The shift's start and stop times;
Which 2 employees worked the shift;
How much rest both employees had between this shift and their previous one (there is a limitation on this so we need to track any violations) - it is not normally the case to have the same 2 employees working together on consecutive shifts.

When you create a query, you can select to view it in SQL then copy and past same in the (#) hash keys in the forum post area then reverse the process to get it back to your query design view after some advice has been given.

So far I've been typing the SQL out rather than using the wizard as I have a *very* rudimentary understanding of it and I generally prefer to see what I'm doing that way. But I'll certainly give that a go to see how I get on.

I've probably bit off more than I can chew with this project, but I look at my frustrations as a learning experience which will hopefully make things easier next time. The assistance that you and other members have offered on this forum has been invaluable, so thank you once again.

 

[PNGBill]

This is keeping track of shifts that have happened in the past, rather than forecasting. In that regard, I currently have a one-to-many join to an employee table.

This makes the task easier.

The shift's start and stop times;
Which 2 employees worked the shift;
How much rest both employees had between this shift and their previous one (there is a limitation on this so we need to track any violations) - it is not normally the case to have the same 2 employees working together on consecutive shifts.

This appears to be quite easy. Can you assume any time not on shift is Rest Time. If so then you only need to track Shift Time and all should be revealed.

 

[PNGBill]

I suggest you change this

tblSHIFT - a table recording each workshift performed. This table includes the following fields:
ShiftID - primary key
ShiftStartTime
ShiftStopTime

tblCREW - a table recording the 2 employees who work each shift. This table includes the following fields:
CrewID - primary key
ShiftID - foreign key
EmployeeID

To:
tblShift
ShiftID - Autonumber Primary Key
ShiftStartTime - maybe Date/Time format to capture the Date and Time
ShiftStopTime - again sugest Date/Time
Employee1 - this will hold EmployeeID for the first shift position
Employee2 - 2nd Employee on shift

If you don't use Date/Time format you would need two fields for shift start and shift stop. One for the Date and one for the time.
Be careful here as shifts starting at 10pm and going to 6am are over two days and need to be accounted for as 8 hours and not 2 days.

Assuming EmployeeID in tblEmployees is an Autonumber then Employee1 and 2 above will need to be Long Number.

Create a form that allows you to select and Employee from tblEmployees for each of the two shift positions. Include a Shift Start and shift Stop control on the form.

You can get fancy here.. If the shift is always 8 hrs then just enter shift Start, if not, have both entered.
You can also have a pce of code on the shift employee controls in the form to ensure the same employee isn't selected for both shift positions.

As the data is input after the shift has been done then data integrity is your only issue here ie ensure the correct data is entered, not if rules on shift hours have been broken.

At what point do you want to identify rest issues? as soon as the data has been entered for a shift ? If so, you could include this in code when the shift data is completed or when the form is closed or have a command button to perform the rest period review.

On this reveiw issue...
How far back do you want to review rest periods ?

 

[toast]

Thanks for the speedy and comprehensive reply.

Your suggested table design makes sense, and I'll give it a go.

If you don't use Date/Time format you would need two fields for shift start and shift stop. One for the Date and one for the time.
Be careful here as shifts starting at 10pm and going to 6am are over two days and need to be accounted for as 8 hours and not 2 days.

I've encountered that problem in the past so I have the shift start and stop fields set to dd mm yy hh:nn, and I tend to use either an input mask or controls for data entries.

The form creation and basic VBA should be within my capabilities, and I'll be able to use a query-based subform to list each employee's shift history rather than a table-based one.

As the data is input after the shift has been done then data integrity is your only issue here ie ensure the correct data is entered, not if rules on shift hours have been broken.

Yes, I just need to flag the violations as having occurred, rather than prevent them from being entered.

At what point do you want to identify rest issues? as soon as the data has been entered for a shift ? If so, you could include this in code when the shift data is completed or when the form is closed or have a command button to perform the rest period review.

Ideally I would like the rest issues flagged as soon as the data is entered. I had envisaged setting the rest calculation event trigger to whenever the employee or start time changed.

On this reveiw issue...
How far back do you want to review rest periods ?

For every shift than an employee works, I must be able to show that they had adequate rest (or not) beforehand. I don't have enough knowledge to know whether this is better handled by having a distinct field in every record specifically to hold the rest interval, or by somehow doing it through queries or reports. If I used the field method, how would that cope if the relevant previous ShiftStopTime was subsequently changed?

 

[PNGBill]

Ideally I would like the rest issues flagged as soon as the data is entered. I had envisaged setting the rest calculation event trigger to whenever the employee or start time changed.

There are a few points you can use to trigger the code that will result in a message box or some other action to alert the operator of any issues.
The code will be the same no matter which event you use and can be copied and pasted to another event if you find it better.
[QUOTEFor every shift than an employee works, I must be able to show that they had adequate rest (or not) beforehand][/QUOTE]
This should easily be provided by a query/sql.

Only thing to consider could be how you want any violations to impact on future shifts but if there is no link yet then this can be ignored for now.
I guess the operator must memo the shift roster officer of any issues.

You are probably ready to work on your query/sql and vba code.
Here you can post what code you have on a specific issue and get good advice on any or you encounter.

It is quite easy for your vba to send an email to someone informing them Fred Smith has not reached his minmum rest time and record the email was sent.

A new table ?? tblShiftAlerts ??
ShiftAlertID
SiftID - shift id that caused the breach alert
EmployeeID
AlertDate
AlertOperator
Just an idea

 

[toast]

Well I don't seem to be getting very far on my VBA/SQL! (Edit - made a little more progress eventually but I'll leave it on here in case I'm going down the wrong line completely)

I have started off with a very basic setup as a building block - one table.
tblShift
.fldShiftID
.fldShiftStart
.fldShiftEnd
.fldEmployee1
.fldEmployee2

My first aim was to generate an SQL statement that would retrieve the previous fldShiftEnd value for fldEmployee1. I came up with the following:

SELECT
 (SELECT TOP 1 Temp.fldShiftEnd
  FROM tblShift AS Temp
  WHERE (Temp.fldEmployee1 = tblShift.fldEmployee1 OR Temp.fldEmployee2 = tblShift.fldEmployee1)
    AND Temp.fldShiftEnd < tblShift.fldShiftEnd
  ORDER BY Temp.fldShiftEnd DESC, Temp.fldShiftID)
AS Previous
FROM tblShift;

Which appears to work.

Next I created a very simple form of tblShift. I added an unbound textbox and a button to help understand how to integrate VBA and SQL. The goal was to click the button which would fill the textbox with the previous fldShiftEnd value of the current record's Employee1.

Edited: This is the VBA I currently have.

Private Sub Command17_Click()
Dim rst As Recordset
Dim strSQL as String
strSQL = "SELECT (SELECT TOP 1 Temp.fldShiftEnd FROM tblShift AS Temp
       WHERE (Temp.fldEmployee1 ='" [COLOR=Blue]& Me![fldEmployee1] & [/COLOR]
       "' OR Temp.fldEmployee2 ='" [COLOR=Blue]& Me![fldEmployee1] &[/COLOR] "') AND 
       Temp.fldShiftEnd < #" [COLOR=Blue]& Format(Me![fldShiftEnd], "mm/dd/yyyy") &[/COLOR] 
       "# ORDER BY Temp.fldShiftEnd DESC, Temp.fldShiftID) AS Previous FROM tblShift;"
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not IsNULL (rst![Previous].Value) Then
Me.Text18 = rst![Previous].Value
Else
MsgBox "There was no previous shift"
End If
Set rst = Nothing
End Sub

The extra set of quotes around the current employee were throwing me (when I get everything figured out that will change because it will be based on an EmployeeID not a name). And getting the date format right.

 

[PNGBill]

I try and think of the task as first being what Variables do I need to achieve the end result.
You then create these with your Dim statements

eg
ThisEmployee As String, RecentRestHrs As Long and so on.
I just plucked some names.

You then put the values for this instance.

ThisEmployee = Me.Employee1

From then on, your code can use ThisEmployee rather then have to go back to the form to get the value each time.

Test your SQL well. I think Top is not as reliable as Max - there may be other advice on this but always test sql's in a few instances to ensure the result is correct.

 

[toast]

Many thanks for the advice once again.

I'll look into Max as well as thoroughly testing the sql.

I'm sure I'll be back with more problems!