Show employees and others in combo box
in [Tables]
|
Prev: Appending table autonumber problem
Next: Field Privacy
From: BruceM via AccessMonster.com on 5 Nov 2009 13:14 I am trying to figure out a way to show employees and non-employees in a combo box. The training database includes training records. Training may be conducted by either employees or outside people. It is easy enough to make a combo box showing employees, of course. The EmployeeID number would be the bound column. I suppose I could make a table for non-employees, and join that to the Employee table in a query, or something like that. Unlike the Employee table, the non-employee information would consist of just a first and last name, and maybe the company (and an Inactive field?). One problem with this approach is that most often an outside person conducts a single training session. Their names would remain on the list unless an Inactive field is checked, or something like that (similar to the Employee table). Some people return fairly regularly, so some names should remain on the list. Others return less often, maybe every year or two, but we don't necessarily know at the time that somebody will be returning in the future. Therefore in most cases a name would remain on the list until some housekeeping is done and unneeded names are marked Inactive. This may mean a lot of asking around to see if a name should remain. There are ways around these difficulties (by having the user elect to include Inactive names in the drop-down, for instance), but it seems there could be maintenance hassles and an inconvenient user interface. I am redesigning an old database. In that one I got around the difficulty by storing the trainer's name, not a number. The combo box list is drawn from the Employee table, and Limit To List is set to False. In this way a non- employee trainer can be typed in directly. There has never been a need to list training sessions conducted by an employee. Rather, inquiries about past training tend to be about the subject of the training or a related document. Once the record is located the trainer name may be of interest, but in terms of searching the trainer name is rarely if ever used. I am violating some normalization principles if I store the actual name, but is there a more normalized approach that is as convenient as the current system of typing in a non-employee name? -- Message posted via http://www.accessmonster.com
From: Jeff Boyce on 5 Nov 2009 13:58 Bruce What about the idea of using a 'person' table to store everyone, and having an [Employee] table for those folks who have additional information? You could use an Autonumber primary key on the [Person] table, and use THAT id to show who is doing the training. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "BruceM via AccessMonster.com" <u54429(a)uwe> wrote in message news:9eabdf83ed433(a)uwe... >I am trying to figure out a way to show employees and non-employees in a > combo box. The training database includes training records. Training may > be > conducted by either employees or outside people. > > It is easy enough to make a combo box showing employees, of course. The > EmployeeID number would be the bound column. I suppose I could make a > table > for non-employees, and join that to the Employee table in a query, or > something like that. Unlike the Employee table, the non-employee > information > would consist of just a first and last name, and maybe the company (and an > Inactive field?). > > One problem with this approach is that most often an outside person > conducts > a single training session. Their names would remain on the list unless an > Inactive field is checked, or something like that (similar to the Employee > table). Some people return fairly regularly, so some names should remain > on > the list. Others return less often, maybe every year or two, but we don't > necessarily know at the time that somebody will be returning in the > future. > Therefore in most cases a name would remain on the list until some > housekeeping is done and unneeded names are marked Inactive. This may > mean a > lot of asking around to see if a name should remain. There are ways > around > these difficulties (by having the user elect to include Inactive names in > the > drop-down, for instance), but it seems there could be maintenance hassles > and > an inconvenient user interface. > > I am redesigning an old database. In that one I got around the difficulty > by > storing the trainer's name, not a number. The combo box list is drawn > from > the Employee table, and Limit To List is set to False. In this way a non- > employee trainer can be typed in directly. There has never been a need to > list training sessions conducted by an employee. Rather, inquiries about > past training tend to be about the subject of the training or a related > document. Once the record is located the trainer name may be of interest, > but in terms of searching the trainer name is rarely if ever used. > > I am violating some normalization principles if I store the actual name, > but > is there a more normalized approach that is as convenient as the current > system of typing in a non-employee name? > > -- > Message posted via http://www.accessmonster.com >
From: BruceM via AccessMonster.com on 5 Nov 2009 15:14 Ah, I see what you're saying. I was thinking of it the other way around. Makes sense. The thing I would still need to figure out is how to handle the maintenance of entries that are typically "one and done" for non-employees, while preserving as active the handful of non-employees who are expected back to conduct more training. Thanks for the idea. Jeff Boyce wrote: >Bruce > >What about the idea of using a 'person' table to store everyone, and having >an [Employee] table for those folks who have additional information? > >You could use an Autonumber primary key on the [Person] table, and use THAT >id to show who is doing the training. > >Good luck! > >Regards > >Jeff Boyce >Microsoft Access MVP > >>I am trying to figure out a way to show employees and non-employees in a >> combo box. The training database includes training records. Training may >[quoted text clipped - 45 lines] >> is there a more normalized approach that is as convenient as the current >> system of typing in a non-employee name? -- Message posted via http://www.accessmonster.com
From: Jeff Boyce on 5 Nov 2009 17:46 You are welcome, and I like your idea of using an [Active] field. Here's a variation ... if you care when the person became inactive, store a date value and change the field to [Inactive]. You can use either the y/n field or the presence of a date to help with your selection criteria for "active" folks to display. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "BruceM via AccessMonster.com" <u54429(a)uwe> wrote in message news:9eacecc020647(a)uwe... > Ah, I see what you're saying. I was thinking of it the other way around. > Makes sense. The thing I would still need to figure out is how to handle > the > maintenance of entries that are typically "one and done" for > non-employees, > while preserving as active the handful of non-employees who are expected > back > to conduct more training. > > Thanks for the idea. > > Jeff Boyce wrote: >>Bruce >> >>What about the idea of using a 'person' table to store everyone, and >>having >>an [Employee] table for those folks who have additional information? >> >>You could use an Autonumber primary key on the [Person] table, and use >>THAT >>id to show who is doing the training. >> >>Good luck! >> >>Regards >> >>Jeff Boyce >>Microsoft Access MVP >> >>>I am trying to figure out a way to show employees and non-employees in a >>> combo box. The training database includes training records. Training >>> may >>[quoted text clipped - 45 lines] >>> is there a more normalized approach that is as convenient as the current >>> system of typing in a non-employee name? > > -- > Message posted via http://www.accessmonster.com >
|
Pages: 1 Prev: Appending table autonumber problem Next: Field Privacy |