Re: Table with 230 Fields....what's a better alternative?
- From: "Brian Wilson" <bwilson@xxxxxxxxxx>
- Date: Wed, 2 Nov 2005 11:28:37 +0000 (UTC)
"Regnab" <p.heywood@xxxxxxxxx> wrote in message
news:1130919059.677115.50220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>I think I get the idea of what you're getting at...
>
> tblEmployee (EmployeeID, Firstname, LastName)
> tblTraining(TrainingID, TrainingDesc)
> tblEmployeeTraining(EmployeeID, TrainingID, TrainingScore)
>
> This makes good sense to me, but not sure exactly how you would make up
> the third table. Normally I would make up a form and the user would put
> data straight into it - enter "John" into name field, and it is
> recorded under FirstName in tblEmployee. How would they enter a
> Training Score when the first 2 values (EmployeeID, TrainingID) need to
> be input into the record as well? I could do it via an SQL insert query
> but I'm assuming you boys have a better and easier way. After all,
> there's 227 training records one employee could have a score against.
>
> What I'm trying to get at is how do you design the Form to allow data
> entry. At the moment I'd like to have the Employee name at the top,
> with a list of training options below, with a combo box for each
> TrainingDesc offering the different scores available.
>
> Thanks for your help,
>
> Cheers
>
> Reg
Those are exactly the right questions to be asking. Almost all developers
would use the same type of 3-table structure to model the situation you
describe, but how do you view and edit the data? Here you could offer a
number of options depending on how much customisation/vba coding you are
prepared to do.
At its simplest, you use a subform with the master/child field set to
EmployeeID. This approach means that the correct training records are
displayed in the subform whenever you move to a new employee on the main
form. It also means that with any additions, the EmployeeID is
automatically added. So now you only need to add the TrainingID and the
score. The TrainingID is added by using a combobox which displays the name
but stores the ID (2 columns with the first set to zero-width). The score
is just a textbox.
OK, that's the simplest but it does have some problems. A combobox with
200-300 entries means that if someone was looking for 'Forklift' but it had
been written as 'Category A Forklift' it would be hard for the user to find
this in all those entries. Better would be where you press a button and
type 'forklift' and all possible matches were shown allowing the user to
select between a few.
There is also the problem that if a user has completed many types of
training, then displaying them in one large list might not be that easy to
look through. You could add a field to indicate TrainingType which might
come from another table such as:
1 Warehouse Skills
2 Health and Safety
3 IT Skills
Then you could break up the long list of training into easy to see bits. If
you needed to do some analysis of these training courses, then you could
even code up the courses so that you allocated a number of digits to
indicate the course content, e.g. digit1=type, digit2=compulsory or not,
etc. This type of 'masked field' allows for fast and flexible analysis, but
may be overkill for the start.
If you want some example sent by e-mail, just let me know if your posted
e-mail is valid (mine isn't) and I'll try and send you something.
.
- References:
- Re: Table with 230 Fields....what's a better alternative?
- From: Brian Wilson
- Re: Table with 230 Fields....what's a better alternative?
- From: Regnab
- Re: Table with 230 Fields....what's a better alternative?
- Prev by Date: Re: Detect When Entering New Record
- Next by Date: Remove orderline from subform when returning order
- Previous by thread: Re: Table with 230 Fields....what's a better alternative?
- Next by thread: Re: Table with 230 Fields....what's a better alternative?
- Index(es):
Relevant Pages
|
