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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|