Main and subform woes

[EternalMyrtle]

If the person in charge is already in the participants table then that's an even better reason to make that an FK field. It can stay in the Events table or better yet, you can have a role field in your junction table and list the roles (PersonInCharge, Presenter, Attendant, ect).

Are the events reoccurring?? So, the event has the same name and subject but the location or other details may change??

 

[Beemerang]

Thank you for your patience, I'm sure this is dragging on longer than you expected! :banghead:

Okay, I have removed all spaces from table fields. Those must have slipped in, I usually avoid them.

I used an obscure table naming system to help me relate them to the right form. Silly, sorry for the confusion.

Several locations are possible so I do have a table for those values. Should I include that in the junction table?

The events are reoccurring yes, so the same course could be presented on different dates at different locations.

Could you elaborate on making the person in charge a FK please?

 

[EternalMyrtle]

If the events are reoccurring, you don't want to be entering every iteration into your table.

So, for example if your event are:

Music of Mongolia
Appalachian Bluegrass
18th Century Opera
etc.

Then I would probably have tblEvents and tblEventDetails. tblEvents would just have the names of the Events and maybe target audience, whatever information is integral and stays the same for each type of event.

tblEventDetails would look like this:

TblEventDetails
TblEventDetails (PK)
EventID (FK to tblEvents)
EventDate
LocationID (FK to Locations Table)
etc.

This is the data particular to each iteration of the event and as such is changing.

You should really read up on table structure in a relational database and normalization...

 

[EternalMyrtle]

Could you elaborate on making the person in charge a FK please?

Ok, I need to get back to my own issues but will answer this one question.

Your junction table will probably look like this

tblEventsParticpants (or whatever you are calling it)
tblEventsParticpants (PK)
EventDetailsID (Fk to tblEventDetails)
ParticipantID (FK to Particpants)
RoleID (FK to Roles table)

Make a table for the roles:

Person in Charge
Presenter
Participant
etc

Or just make them a value list in tblEventsParticpants and not an FK

tblEventDetails is for the specific changing aspects of the events.

 

[Beemerang]

I think I'm getting a little overwhelmed and not thinking straight at the moment.

I do have a CourseEvent table that lists course title, date and location. I'll try to figure out how to link it, I'm sure I've exhausted your good will by now!

Thanks again for your time.

L

 

[EternalMyrtle]

It's not easy and is overwhelming at first. Keep at it and you will get it!

Definitely read up about relational database structure and normalization though.

 

[EternalMyrtle]

I gave you slightly incorrect info yesterday: you won't need a lookup on EventID control in your subform because that will be the "child" field. That value will be set based on the EventID in your main form.

You don't even need to show EventID in your subform at all so long as the master/child links are properly set.

You do need the lookup for ParticipantID so you can select which people will attend a specific event.

Sorry about the mix up! I must have been getting tired or hungry

If you choose to make a separate table for Event Details as I suggested above then the main form would be based on tblEventDetails and EventID in that table would be a lookup. I highly suggest doing this---at the very least you should be using a value list for the event names.

The link master/child field setting would still be on EventID in both the main form and subform.

I hope this is clearer. Take care and good luck with your project!

 

[Beemerang]

Thanks again for all your help!

 

[CazB]

Only just spotted this... I have an (old) database that is very close to doing what you wanted, lol....

Mine went about it in a slightly different way - basically, you 'register' someone's need to do a course, and then you Book them onto an Event... it allowed us to build a Waiting List of Attendees so we could see which courses we needed to run!

Let me know if you'd be interested in having a look

 

[Beemerang]

Hey CazB,

Thanks for the reply! That would be wonderful, I realize how much I have to learn so any material/info/examples are really valuable to me.

Beem

 

[CazB]

ok, bear with me while I strip the data

 

[Beemerang]

You are so kind, thank you!

 

[CazB]

Here you go... left some in but mixed up the names, so you can see how it works... bear in mind I wrote this about 12 years ago and my knowledge has come on a lot since then, lol.... but it worked, at the time!

 

[Beemerang]

You rock! Thanks, will check it out.

Beem