Other table design solutions for storing multiple employee ID's

[laskowv]

I have a table which holds the ID, DivisionID, Division term (ex: 2007-2009,
2009-2011), followed 10 fields (type = number) for the 10 officer positions.
I have 52 divisions and 178 terms that have to be tracked (1921-2099). Each
10 Division Officers need to be maintained; for example here are the 10
offices and the EmployeeID numbers:

ID DivID Term Term Pres 1VP 2VP Chaplain RS Reg. Treas. Hist.
CS Lib Parl
40 29 88 4/30/2009 26694
29776 35877 32121 36207 34987 34985 36942 34863 36497 26088
41 29 90 4/30/2011 29776
35877 2461 36311 36053 36498 36575 37209 33860 36207 26694

This allows me to only have about 5000 records. If were to make the table
just have a field called officer position and the the employeeid; it would
cause the file to hold over 92,000.

My issue is two- fold:
1) I have a combo box on the form that uses the employee number from the
field to each of the 10 fields. It works fine, but the form is very slow to
open -- but once open it works great.

2) I need to generate reports from this table. I need to list all of the 10
officers names. I started creating the query and linked the President field
to the Employee table to get the name; but when I went to link the 1st VP
that's when Access "yelled" at me.

How do I link to the Employee table for each of the 10 officers in order to
get their names? Is there another way?

Please help.

 

[John W. Vinson]

I have a table which holds the ID, DivisionID, Division term (ex: 2007-2009,
2009-2011), followed 10 fields (type = number) for the 10 officer positions.
I have 52 divisions and 178 terms that have to be tracked (1921-2099). Each
10 Division Officers need to be maintained; for example here are the 10
offices and the EmployeeID numbers:

ID DivID Term Term Pres 1VP 2VP Chaplain RS Reg. Treas. Hist.
CS Lib Parl
40 29 88 4/30/2009 26694
29776 35877 32121 36207 34987 34985 36942 34863 36497 26088
41 29 90 4/30/2011 29776
35877 2461 36311 36053 36498 36575 37209 33860 36207 26694

This allows me to only have about 5000 records.

Why's that?

If were to make the table
just have a field called officer position and the the employeeid; it would
cause the file to hold over 92,000.

Certainly a better design.

My issue is two- fold:
1) I have a combo box on the form that uses the employee number from the
field to each of the 10 fields. It works fine, but the form is very slow to
open -- but once open it works great.

It's got to populate all ten combos, so yes, it'll be slow.

2) I need to generate reports from this table. I need to list all of the 10
officers names. I started creating the query and linked the President field
to the Employee table to get the name; but when I went to link the 1st VP
that's when Access "yelled" at me.

You would need to add the officer table to the query TEN TIMES. Access will
alias the names, e.g. Officers, Officers_1, Officers_2, Officers_3 and so on.

How do I link to the Employee table for each of the 10 officers in order to
get their names? Is there another way?

The other way is... to normalize your data. You have repeating fields (one
field for each type of officer). The proper design would have three tables:

Officers
OfficerID
LastName
FirstName
<etc etc, I'm sure you have this>

Offices
OfficeID <autonumber, Primary Key>
Office <Text, e.g. President>
<any other info about the office, e.g. its statuatory term>

OfficeHolders
OfficeID <link to Offices>
OfficerID <link to Officers>
StartOfTerm <date/time>
EndOfTerm <date/time>
<any other info about this person with regard to this office>

A Form based on Officers, with a Subform based on OfficeHolders (using
OfficerID as the master/child link field and a Combo Box based on Offices)
will let you easily enter data for a person; the opposite structure, with a
Form based on Offices with a subform based on OfficeHolders, using OfficeID as
the master/child and a combo for the name, will let you see a history of who
has held which office.

A Crosstab Query can recreate your current view of the data if that would be
useful. Your Report would be very easy to create, just join the three tables.

 

[Klatuu]

John Vinson mentioned the problem with having 10 combos on a form and the
negative impact on performance it would have. I had such a situation about 3
or 4 years ago. It had 6 list boxes rather than combos and the form took
almost 2 minutes to load. I came up with the following strategy that makes
the problem invisible to the users.

All the list boxes had no row source and the height property was set to 0.
Above each list box was a command button with the name of the list so the
user could click the list he wants. At that point, the Click event of the
list box populates the list:

Private Sub cmdBillProdOffering_Click()

DoCmd.Hourglass True

Call ResetScreen(6)

With Me.lstBillProdOffering
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT ProjectID " & _
"FROM actual_res_export " & _
"WHERE ProjectID <> 'Billable' And BillCat = 'unbillable' "
& _
"ORDER BY ProjectID;"
End If
.Height = 3015
.SetFocus
End With
DoCmd.Hourglass False

End Sub

The ResetScreen function was used to keep the list boxes in sync:

Private Sub ResetScreen(lngSelector As Long)
If lngSelector = 6 Then 'BillProdOffering
With Me.lstMActivity
.Height = 0
.RowSource = ""
End With
Me.lblMactivity.Caption = "All Included"
Me.lblMactivity.ForeColor = 0
End If

If lngSelector >= 5 Then 'MActivity
With Me.lstActivity
.Height = 0
.RowSource = ""
End With
Me.lblActivity.Caption = "All Included"
Me.lblActivity.ForeColor = 0
Me.lblMactivity.Caption = "All Included"
Me.lblMactivity.ForeColor = 0
End If

If lngSelector >= 4 Then 'Activity
With Me.lstBillNetwork
.Height = 0
.RowSource = ""
End With
Me.lblBillNetwork.Caption = "All Included"
Me.lblBillNetwork.ForeColor = 0
End If

If lngSelector >= 3 Then 'BillNetwork
With Me.lstPool
.Height = 0
.RowSource = ""
End With
Me.lblPool.Caption = "All Included"
Me.lblPool.ForeColor = 0
Me.lblActivity.Caption = "All Included"
Me.lblActivity.ForeColor = 0
End If

If lngSelector >= 2 Then 'Pool
With Me.lstHomeRoom
.Height = 0
.RowSource = ""
End With
Me.lblBillNetwork.Caption = "All Included"
Me.lblBillNetwork.ForeColor = 0
Me.lblHomeRoom.Caption = "All Included"
Me.lblHomeRoom.ForeColor = 0
End If
End Sub


Since it is a combo, you might want to just make the combo visible/invisible
rather than using a height property, but other than that, it concept is the
same.

 

[laskowv]

Why's that?


Certainly a better design.


It's got to populate all ten combos, so yes, it'll be slow.


You would need to add the officer table to the query TEN TIMES. Access will
alias the names, e.g. Officers, Officers_1, Officers_2, Officers_3 and so on.


The other way is... to normalize your data. You have repeating fields (one
field for each type of officer). The proper design would have three tables:

Officers
OfficerID
LastName
FirstName
<etc etc, I'm sure you have this>

Offices
OfficeID <autonumber, Primary Key>
Office <Text, e.g. President>
<any other info about the office, e.g. its statuatory term>

OfficeHolders
OfficeID <link to Offices>
OfficerID <link to Officers>
StartOfTerm <date/time>
EndOfTerm <date/time>
<any other info about this person with regard to this office>

A Form based on Officers, with a Subform based on OfficeHolders (using
OfficerID as the master/child link field and a Combo Box based on Offices)
will let you easily enter data for a person; the opposite structure, with a
Form based on Offices with a subform based on OfficeHolders, using OfficeID as
the master/child and a combo for the name, will let you see a history of who
has held which office.

A Crosstab Query can recreate your current view of the data if that would be
useful. Your Report would be very easy to create, just join the three tables.

I had originally built the file this way, but I think the confusion comes
from not ever using the continuous forms before.

Also, on the combo boxes, when linking master/child, I end up having to
"manually define" it because the text exceeds the message box and I can't
expand the window to see all 3 options. Have you encountered this at all?
I've kept short form names for this reason but it always scrolls past the
right side of the window.

Valerie