Use the result of one query to generate another query

[oblivion86]

Hi all,

I have 2 tables, Event and Person Particulars.

In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).

Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.

If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?

Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.

Appreciate your advice.

Thanks.

 

[plog]

You need 3 tables for this. 'Event', 'Person Particulars', and a junction table (http://en.wikipedia.org/wiki/Junction_table) that I will call 'EventPeople'.

A junction table helps define a many to many relationship. You have many events, you have many people, many people can be assigned to one event and one person can be assigned to many events. To do this you need a table that links the ids from Events to the ids in Person Particulars.

Once those relationships are set up with your junction table you will be able to run a query to see what people are associated. EventPeople will have probably just 2 fields, a foreign key from Event and a foreign key from Person Particulars:

EventPeople
PersonID, EventID
17, 4
17, 6
23, 5
23, 4
22, 1
22, 4

 

[oblivion86]

Thanks for your reply.

However, I am a little confused, for this junction table that I require, what data is it supposed to contain?

My database allows people to enter the event and the people associated with each event. What data then shall I input in this junction table (i.e. eventpeople)?

My event ID is an autonumber and I have no idea how many events there are going to be, most likely an infinite number of events.

From what i understand, the junction table should contain the fields:
1)EventID
2)Person ID

But what data should I fill the table with?

Thanks.

 

[Brianwarnock]

I have a hunch that this database is not normalised, that a record in the events table looks like

Eventid    people
2           tom dick harry

or their social security numbers

Brian

 

[plog]

You should fill the table with the unique IDs of the other 2 tables.

You have a unique ID for the events (i.e. EventID). You should have one for each person as well (i.e. PersonID). The junction table determines which people and events go together.

 

[jdraw]

oblivion86,

I think part of your issue, based on your questions to plog, is an understanding of tables and relationships. Brian has also identified this through his lack of Normalization "hunch".

You have a Many to Many set up as I understand your post.
Many Events can involve Many People
and Many People can participate in Many Events


This is the "Many to Many" that gets resolved via a junction tables (as plog said) to two 1 to Many relationships

Event ---->EventParticipant<------Participants


where
EventTbl
EventId PK
EventName
EventLocation
EventDate
other Event info

ParticipantTbl
ParticipantID PK
ParticipantFName
ParticipantLName
other participant specific info


EventParticipantTbl
EventParticipantId pk
EventID FK
ParticipantID FK
any other fields specific to this event and this participant

The fields in purple would form a unique compound index to prevent duplicates.
Some developers would use these two fields as PK (compound PK), but I prefer a separate single field PK and to make a unique compound index of these 2 FK fields.

More than one way to set up your tables.

For a free video on junction table see
https://www.youtube.com/watch?v=7XstSSyG8fw

 

[oblivion86]

Hi jdraw. I understand that I have to link the event tbl and the participant tbl with a junction table.

However, my database is set up such that the user will key in the event details and participant details.
I won't have a fixed data on which participant belongs to each event.

So for data to be input into the junction table (I. E. Event Id and participant id) do I have to set up these 2 fields to "lookup" the data from the respective fields that the user have keyed into the event tbl and participant tbl?

 

[oblivion86]

Hi guys,

I found a sample database that keeps a record of artists and the albums they appear in.

I understand that this is somewhat similar to what I am trying to achieve with my event participant database.

However, the greatest difference would be that for the artist-album database, it is pre-determined that this artist would appear in this album from the junction table.

In my database, the participants of a certain event and the event details itself are entered by the users of the database themselves, therefore I do not have a pre-determined list of which participant is attending which event.

So then how do I tag a participant with a unique ID (participantID) to an eventID?
*a participant can be part of multiple events.

Thanks in advance.

 

[oblivion86]

Hi guys,

I went through some of the posts on this forum and I think i got an idea how to complete my database and I just need some confirming from you guys.

I would like to populate my junction table during my data entry. The junction table would include 2 fields, i.e.Event ID and Person ID.

To do this, I have to set both these fields to autonumber and use these fields as my entry field in my entry form.

Am I correct so far?

The problem i faced is that when i used autonumber as the data type for these 2 fields in my junction table and select both fields and click on "primary key" icon on the ribbon (to create a junction table) it informs me that I am unable to do so.

Can someone enlighten me?

Thanks.

 

[jdraw]

YES!!The junction table would include 2 fields, i.e.Event ID and Person ID.

NO! I have to set both these fields to autonumber
Set these fields to Long Integer.

 

[oblivion86]

If I set it to long integer then it won't be populated when the user does the data entry, unless I set it to look up the same field In tblevent and tbl person.

?? Still confused??