A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Other table design solutions for storing multiple employee ID's



 
 
Thread Tools Display Modes
  #1  
Old July 7th, 2009, 05:01 AM posted to microsoft.public.access.tablesdbdesign
laskowv
external usenet poster
 
Posts: 15
Default Other table design solutions for storing multiple employee ID's

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.


  #2  
Old July 7th, 2009, 06:58 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Other table design solutions for storing multiple employee ID's

On Mon, 6 Jul 2009 21:01:01 -0700, laskowv
wrote:

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.
--

John W. Vinson [MVP]
  #3  
Old July 7th, 2009, 04:42 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default Other table design solutions for storing multiple employee ID's

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.

--
Dave Hargis, Microsoft Access MVP


"laskowv" wrote:

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.


  #4  
Old July 8th, 2009, 05:45 AM posted to microsoft.public.access.tablesdbdesign
laskowv
external usenet poster
 
Posts: 15
Default Other table design solutions for storing multiple employee ID'



"John W. Vinson" wrote:

On Mon, 6 Jul 2009 21:01:01 -0700, laskowv
wrote:

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.
--

John W. Vinson [MVP]
Thank you so much. I just wanted to verify that on the OfficeHolders table -- I should let the "system" auto-generate a "key" for me (for example: ID (autonumber)?


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
  #5  
Old July 8th, 2009, 05:45 AM posted to microsoft.public.access.tablesdbdesign
laskowv
external usenet poster
 
Posts: 15
Default Other table design solutions for storing multiple employee ID'



"John W. Vinson" wrote:

On Mon, 6 Jul 2009 21:01:01 -0700, laskowv
wrote:

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.
--

John W. Vinson [MVP]
Thank you so much. I just wanted to verify that on the OfficeHolders table -- I should let the "system" auto-generate a "key" for me (for example: ID (autonumber)?


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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.