Is there a better table design?

[toast]

I've attached a screenshot of my current table relationships which is hopefully relatively self-explanatory.

The database keeps track of who worked various shifts at various depot locations. There are always 2 employees crewing each shift (never more, never less).

As things stand at the moment, the details of the shift are entered on a form based on tSHIFT. There is a subform based on tCREW for the 2 employees who worked that shift.

Is this the correct table design, or should I somehow have integrated the 2 employees working each shift as distinct fields in the tSHIFT table?

The problem I'm running into is that I need a dropdown list of employees in the subform table to simplify data entry, but from reading various articles dropdown lists in tables are not a good idea...?

 

[stopher]

I've attached a screenshot of my current table relationships which is hopefully relatively self-explanatory.

Can't see any issues.

Is this the correct table design, or should I somehow have integrated the 2 employees working each shift as distinct fields in the tSHIFT table?

don't create two fields. It will give you a head when you have to do things like list all shifts John was on. Instead, get your form to ensure that no more than two records are added. The way I would do it is get the system to auto-generate the exactly two records for the shift. Then allow the user to maintain the two records (but not add records).

As belts and braces you could maintain integrity by creating another table with just two records and having value 1 & 2. Then have a link to a new field in your crew table so that the new field can only be 1 or 2. Then add an index so only 1 & 2 can be added for each crew ID.

The problem I'm running into is that I need a dropdown list of employees in the subform table to simplify data entry, but from reading various articles dropdown lists in tables are not a good idea...?

You can have dropsdowns in forms no problem. Just don't set them up in your tables.

hth
Chris

 

[toast]

That's great, many thanks.
I'll give it a go trying to auto-generate the records in the subform.

 

[toast]

I've been working through some VBA to get 2 records automatically generated.

I can get it to work, but it is messy.

The tCREW has a composite primary key made up of the Shift's ID and the Employee's ID. This means that the first record cannot be saved until it has an employee assigned to it (but at this auto-generate stage we don't know who that would be).

The solution I thought was to create a dummy employee and set that as the default (or add it with the VBA).

But this now means that the second record will have the same composite primary key as the first record when it is generated. So now I have to generate 2 dummy employees and assign them with VBA.

An alternative solution I thought of was not to have a composite primary key for the tCREW table. Instead, add an autonumber ID field to the table which would mean that an employee would not have to be added in order to save the record.

Which is the better solution?

Using the second idea (new field in tCREW table) this seems to work:

[COLOR="Blue"]Private Sub[/COLOR] Form_Open([COLOR="blue"]Cancel As Integer[/COLOR])

[COLOR="DarkGreen"]'error handling[/COLOR]
[COLOR="blue"]On Error GoTo[/COLOR] Err_Insert

[COLOR="DarkGreen"]'set appearance[/COLOR]
Me.Form.RecordSelectors = [COLOR="blue"]False[/COLOR]
Me.Form.NavigationButtons = [COLOR="blue"]False[/COLOR]
Me.SubForm.Form.RecordSelectors = [COLOR="blue"]False[/COLOR]
Me.SubForm.Form.NavigationButtons = [COLOR="blue"]False[/COLOR]

[COLOR="DarkGreen"]'set permissions[/COLOR]
Me.Form.AllowAdditions = [COLOR="blue"]True[/COLOR]
Me.SubForm.Form.AllowAdditions = [COLOR="blue"]True[/COLOR]

[COLOR="DarkGreen"]'generate a new shift record[/COLOR]
Me.SetFocus
DoCmd.GoToRecord , , acNewRec
Me.ShiftDate = Date - 1
Me.Form.AllowAdditions = [COLOR="blue"]False[/COLOR]

[COLOR="DarkGreen"]'generate 2 new records in subform[/COLOR]
Me.SubForm.SetFocus
Me.SubForm![Capacity] = "Driver"
DoCmd.Save
DoCmd.GoToRecord , , acNewRec
Me.SubForm![Capacity] = "Loader"
Me.SubForm.Form.AllowAdditions = [COLOR="blue"]False[/COLOR]
Me.ShiftDate.SetFocus

[COLOR="DarkGreen"]'Error Handling[/COLOR]
Exit_Insert:
[COLOR="blue"]Exit Sub[/COLOR]

Err_Insert:
MsgBox Err.Number & Err.Description
[COLOR="blue"]Resume Exit_Insert[/COLOR]

[COLOR="blue"]End Sub[/COLOR]