Hey Everyone,

Right now I'm beating my head on my monitor trying to solve this problem. I'm in the Air Force Reserve and in charge of training for my duty section. I'm trying to setup a database that shows the progression of training for each individual in the section.

Here is all the pertinent information that needs to be included in the database

Table 1: (WorkCenterMembers)
Last Name (text)
First Name (text)
Rank (text)
Training Status (checkbox)
Last Skill Level Completed (Choice between 3,5,7,9)
Career Field (text)

Table 2: (MTLlist)
Task Number (text)
Core Task (Yes/No Checkbox)
Task Title (Text)
Training Level (Choice between 3,5,7,9)
UTC Training Requirement (Yes/No Checkbox)
UTC Code (Text)
Task Training Completed (Yes/No Checkbox)

These are the minimum requirements.


I've setup a database that uses a form (form1) to add new personnel to table 1 while at the same time creating a table from the information in table 2. The new table is named after the last name of the new person entered into table 1. This setup would work for a limited number of personnel but with many it would be hard to manage.

My big issue is the number of records contained in table 2. At this point there are 209 records and will gradually grow as more training task are added. Each one of these task(records in table 2) needs to relate to each person listed in table one. In otherwords, each person in table 1 is assigned 209 tasks(records) in table 2.

I've also used form 1 to add a new field to table 2 with the new personnels last name as the field name but would run into the same problem stated above in database management.

Well, now you know my problem, any help is greatly appreciated and thanks in advance. I've attatched a copy of the db.

Ed

hi duke,

i'm not as expert as many of the others here but this is my take on it:

I would suggest having 3 tables rather than 2
The first can be your WorkCentreMembers - but you should define a unique field to use as a primary key - a username or a combination of firstname_lastname to make a unique combo. This will make it easier later on....

In this first table, you should only have your members data - ie key field (as above), name, rank and career field.

The second table is your task list - containing 209 items. Task Number (this could be your primary key field if this is unique), and the rest of the fields you outlined in MTList can go in here except the "Task Training Completed" field

The third table is where you can store your results. You should set up an Autonumber field called sth like "ID", then your key field from your Members table (eg username), task number, a date field (with default value as Date, you can then use this later for determining the last skill level acquired).

There will be some more work to be done to setup the queries and forms but this should give you a start on your tables....

good luck!

Ed,

I'm sorry to say, (IMHO) you have some fundamental problems. :eek:

1) Table 1 has no (apparent) primary Key.
I suggest you add an AutoNumber field, "WorkCentreMemberID"

2) Best to avoid embedded spaces (and also special characters) in field names - these add to life's difficulties in several places - I (and many others) use MixedCaseNames which are easy to read and understand, once you get used to them.

3) Adding a table for each person is an inherently and fundamentally BAD idea, and no-one with any depth of experience is likely to recommend this as an approach to solving a many-to-many relationship issue (which is what you have here - and you've tried to dilute it to a one-to-many by building so many tables).
3a) What are you going to do when you have two "Smiths" ?
3b) When you want to query the data, you will need a separate query for each person - a maintenance nightmare !
3c) If a new training task comes along, you'll have to update many tables (with the same update)
3d) Many more reasons besides !!!!

I suggest you split table 2 into two tables - one to house the task definitions - presumably these don't change per person????

Table 2: (MTLlist)
TaskNumber (text) - if this is really a "number" define the field as such. In either case, this would be a good candidate for a primary key.
CoreTask (Yes/No Checkbox)
TaskTitle (Text)
TrainingLevel (Choice between 3,5,7,9)
UTCTrainingRequirement (Yes/No Checkbox)
UTCCode (Text)


And a second to track the completion - presumably this is the only thing that varies per person - there are at least two possibilities here - but I'll limit myself to one suggestion...

Table 3: (TaskTracking)
---------------
TaskNumber (Text - or Number - as per above)
WorkCentreMemberID (long Integer)
TaskTrainingCompleted (If you made this a Date/Time field, instead of a yes/no, you could later tell when someone completed a training task - easy to implement up-front but hard to retro-fit). If you can categorically, absolutely, emphatically, guarantee that no-one will ever want to know this, I would still include it anyway :p !

Make all fields "Required"
Make a composite Primary Key, composed of TaskNumber and WorkCentreMemberID

In the TaskTracking table you would only add records for a completed person/task - non-completion would therefore be discernible by the absence of a person/task combination - this is quite easy to report on with the right query. Making the Person/Task combination the composite Primary Key will also prohibit (otherwise) possible duplication of entries.

Hope this points you in a better direction.

Regards

John.

Hmmm... I see Shammy beat me to the punch on this one by a couple of minutes - with mostly the same sugggestions.... I'll leave my post anyway - no offence intended to Shammy.

Furthermore I agree with shammy, and I previously missed the boat on Table 1 containging...
Training Status (checkbox)
Last Skill Level Completed (Choice between 3,5,7,9)

I don't know what "Training Status" is supposed to indicate ???

And shammy's statement about determining Last Skill Level Completed further supports my assertion of making the TaskCompleted a Date/Time field, as Shammy also suggested.

I would, however, recommend against using FirstName + LastName as a Primary Key in the person table - unless you want to make it impossible to have two "John Smith"s etc. Do Air Force Reservists have a "Serial Number" akin to an army serial number ??? If so, this would be a good/better candidate for a Primary Key for this table.

HTH

Regards,

John.

hehehe - glad to see I was on the right track and not giving duff advice :cool:

i just read a posting from Doc Man about creating primary key fields - recommends as short as possible. At our company, each PC user has a unique 8 char username - and I use this as the key. Makes it much easier if the key for a table of people is still recognisable as that person but I concede that firstname_lastname is NOT a good idea for a key!

btw duke - you never did attach your db.... :eek:

Take a look at my Many-to-Many (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany) sample database. Your three tables will be tblMember, tblTask, tblMemberTask. The member task table will contain the primary keys of the other two tables - that makes the relationship. It will also contain other information such as completionDate and grade.