Help with relationships

[rhett7660]

Hello..

Here is what I have. The database is made up of three tables (see below and see attachment). I have it set as one event to many ethnicity's and many personnel with the event ID as the PK in all three. Did I do this correctly? I thought I did and I had a form with each one of these running correclty but now it is not working.

tblEvent
EventID(PK)

tbleventEthnicity
EventID (FK)

tblPersonnel
EventID(FK)

See attachment for the other fields.

Can someone just check to see if I set this up correctly or what I should do differently?

Thanks

 

[CraigDolphin]

To be clear, EventID is only be the PK of the Event table. The field EventID in the other two tables are foreign keys.

However, I think you may have a design glitch. Consider that a single ethnicity may be represented by several events. In your design, you must re-enter the same 'ethnicity' information each time.

Try adding a table tblEthnicity with any Ethnicity-specific field in it. It will have its own pk EthnicityID.

Then create a junction table to link EthnicityID to EventID.
tblEventEthnicity
EventEthnicityID (Pk)
EventID
EthnicityID

Now, events can point to one or many ethnicities, or conversely ethnicities can be linked to one or many events. Data about ethnicities is only entered once. Data about Events is only entered once.

Looking at your fields though I'm a bit unsure of what's going on....how can ethnicity have a gender?

 

[rhett7660]

Craig..

Thank you very much... What I was thinking and what the end user wanted to see was this:

Event 1
Event Ethnicity Amarican Indian Male 23
Event Ethnicity American Indian Female 2
Event Ethnicity Hispanic Male 44
Event Ethnicity Hispanic Female 0
Etc

Personnel assigned to Event 1
John Doe Clerk
Jane Doe Supervisor

Hope that make sense?


P.S. thank for the FK and PK.. forgot to add that in that.. Thanks

 

[CraigDolphin]

Ok, let's review to see if I understand you.
You want to store information about events, about personnel who work at the events, and also about counts of groups of people attending the event that are to be classified using descriptors like ethnicity and gender?

To me that suggests a structure like

tblPersonnel
PersonnelID (PK)
etc

tblEvents
EventID (PK)
EventLocation
etc

tblEventPersonnel (junction)
EventPersonnelID (PK)
EventID (FK)
PersonnelID (FK)

tblEventAttendeeGroups
EventAttendeeGroupID (PK)
EventID (FK)
EthnicityID (FK)
GenderID (FK)
EventattendeeGroupCount

tblEthnicity
EthnicityID (PK)
EthnicityDescription

tblGender
GenderID (PK)
GenderDescription

I haven't described all the other fields but these should give you the nuts and bolts for the structure you need.

You need the junction table between personnel and event because many personnel could attend one event, and one personnel might attend many events.

Now, once you have the data stored in this kind of structure, it's a simple matter to query multiple tables at once to produce whatever report you want the user to see.

You might also consider creating a muliple field index on tblEventAttendeeGroups to prevent duplicate entries for unique combinations of EventID, GenderID, and EthnicityID.

 

[rhett7660]

Craig..

Thank you very much. Question, how do you know when to use a junction table? I have seen it a couple of times but never on when to you use one?

Thanks

 

[rhett7660]

Craig..

Something like this?

See attached.

Thanks again for your help!

 

[CraigDolphin]

Yep...that's it.

You use junction tables whenever you are faced with a potential many to many situation.
Consider:
Personnel can attend many events each.
Each event can also be attended by many personnel.

think how you would try to satisfy both realities without a junction table...

If you place a personnelID field in the events table (to deal with one person attending multiple events) you now have a conundrum on how to deal with two or more personnel attending the same event. What do you do without a junction table? Add more rows to the event table even though it's the same event?

Aside from the fact that such an response would break all the database 'rules' (normalization etc), consider how complicated that would make your life if you ever want to count how many events existed in a given time span. And how about attendee information? Which of the many 'same event' rows do you link that information to? Do you pick just one or do you replicate the same information for each line in the events table? as you can imagine, the burden to manage your data then shifts away from the software and lands firmly on your head. (ouch!)

And the reverse is true if you try to include the eventid field in the personnel table. Suddenly, each personnelid can only ever attend one event or you start multiplying rows in the personnel table. The database then doesn't know how to track each person since they have multiple id values, and there's always the chance that you have two people named John Smith if you're unlucky.

Anytime you're tempted to start adding extra rows to a table that differ only by one piece of information, chances are you mis-identified a many to many situation and you may have a big problem reorganizing your existing data. That's why developers on this forum often talk about the need to speed a huge amount of time in the table design phase. It's critical to identify all those relationships correctly at the beginning of the project.

 

[CraigDolphin]

Also, just for the sake of being pedantic...you should also use the full GenderID naming convention in your gender table. Naming conventions can save you grief when writing code, dlookups, etc only if they're consistent across all tables.

 

[rhett7660]

Yeah I know.. I took over this database and some of the naming is well.... just flat out bad. I would change it, but there are like 6 or 7 other forms and numerous reports that are based off it.... nice huh..

 

[rhett7660]

Thank you very much again! Really help full....

 

[CraigDolphin]

Glad to have helped. If this is an existing database and you're going to re-do the table structure, you're going to have to redo all the forms and reports that reference them anyway....so why not go the whole way? Up to you, of course

 

[rhett7660]

Yeah I know.......... I don't look forward to that lol..

 

[rhett7660]

Craig..

One more question. When I do a query, do I need to include the junction tables?

 

[CraigDolphin]

Well, it depends ont the what the query is meant to be showing but, if you include the personnel table, and you include the events table, then yes, you should also include the junction table so that the query can show the related data correctly.

 

[rhett7660]

Gotcha.

Thanks again.

 

[rhett7660]

Craig..

I am having a little trouble building the query that will be the bases for a form. Do I need to create seperate queries based on the info?

Here is what I am looking to do..

Tab 1 on the form: event info
Tab 2 personnel assigned to said event
Tab 3 Ethnicity break down

Would I create a query for each tab? When I bring all of the tables in for one query, I don't think the query likes it to much. I should I go about creating a query or queries for getting the required fields for data entry?

I have been working on this off and on all weekend..

 

[rhett7660]

I am attaching some screen shots.

The first one with three tables I can see information.

The second one with all the tables involved, no informatin is being produced.

Thanks again

 

[rhett7660]

I can post more if needed. But I am hoping you can see what I am doing wrong...

 

[CraigDolphin]

Rhett,

Your tblEventEthnicity looks a bit different than the last time you posted. I strongly suggest deleting that table completely and instead using the tblEthnicity and tblGender that I suggested in my earlier help.

Your 'form' actually needs to be a main form bound to tblEvents, with a tab control on it. The first page of the tab control could be used to place controls bound to fields in tblEvents.

The second page of your tabl control is where you place a subform to display event personnel. This subform will be in continuous form view and is bound to tblEventPersonnel. The master-child key field between your main form and the subform is going to be EventID. There will be two controls on the subform. EventID and Personnel ID. EventId can be invisible. PersonnelID will be a combo that shows the person's name (you can concatenate the first and last names in the rowsource query) but stores the person's ID.


The third page of your tab control will also be used to place another subform. This will be bound to tblEventAttendeeGroups and, again, will be in continuous form view. As before, the master-child key will be the Eventid field. This subform will have 4 controls on it. EventID (invisible). EthnicityID (combo that displays the ethnicity name but stores the ethnicityID), GenderID (combo that shows the gender description but stores the genderid), and EventAttendeeGroupCount (which will be a text control showing the number in that group).

 

[rhett7660]

Craig..

You are right, I didn't even notice that. That is the old table. Weird. I will sort it out and try what you suggested.

Thanks again