Need help with Registration Database.... 1

[freebd99]

Hi all,
I have a database I designed for use on my job. The database works similar to a college regsitration system. Student A will have the same ID but will attend various classes throughout his/her 4 yrs of college.

My database has the following tables:
Participant Table: partID(prim key) and contact info.
Registered Trainings: regTrainID(prim key), partID(foreign key)

From the Reg Trainings table there are links to the class schedules and locations etc. which I don't think are relevant to the problem I'm having.

Now that the first year is completed I am receiving registrations for the second year and both with new participants and returning participants. The problem is that I can't add new registrations to the returning participants. Access tells me I would be creating a duplicate primary key. The partID in the Reg Table should have duplicates at least that's what I'd like to have happen. The only way the dbase works is if I remove partID in the participant table as the primary key.

I hope I've made this situation clear. I look forward to everyone's comments and suggestions.

Freebird

 

[lespaul]

Unless I completely misunderstood your post, you need to normalize the database and add a third table to track the one-to-many relationship between participants and training.

So, you have your participant table:

ParticipantID, Name, Address, PhoneNumber, etc.

Then you should have your Training Table
TrainingID, ClassTitle, ClassDate, etc.

THEN you should have a Participant_Training table
ParticipantID, TrainingID

and make both of those fields the primary key. Which would allow a participant to have multiple trainings.

For more information on normalization, check out Thread700-628486

Leslie

 

[freebd99]

Thanks so much Leslie for your reply. BUT...

The Registered Training table is supposed to work like the third table you described. The reg table consists of the regTranID(pk), partID(fk), payment type, training year and few other fields.

For the training info, ie Title, Date etc I have two table Core and Electives (types of trainings we have) The Core and Elective tables have the training info. Then I have ElectDetail and CoreDetails. Those tables connect to the Reg Training table as follows:

ElectDet: regTrainID and electID(both as pk)
CoreDet: regTrainID and coreID (both as pk)

So that the registration table asks as bridge to the participant and the trainings that participant is taking. I am trying to visualize what you are suggesting to see if there is another way to set up these table.

Thanks again and I look forward to any additional comments and suggestions.

Freebird

 

[freebd99]

Leslie,
I got it to work. YEAH!! Thanks so much.

Freebird

 

[lespaul]

What did you do?? Make the primary key of the Registration table both the PartId and the TrainingID?

Leslie