Flat File Query

[mma3824]

Hello -

I am a relatively new to Access and am very stumped by building queries. The database that I am working with has multiple tables with a good number of fields to each of them. Example of the fields in the tables would be from left to right: Individual ID, date of birth, date of event, event name, location name. Each location would have their own table but there could easily be individual ID's that are the same over multiple locations (ex. there is an individual named #1 in many different locations but each #1 is a different individual).

How do I put together queries to look at how many similar events there are between the different Tables?



Thanks in advance!

[rpeare]

There's really no way to guide you you've asked a very open ended question referring to tables we don't know the construction of, nor whether they are properly normalized. Queries can be defined by left joins, right joins or inner joins.

To give you an example create the following two tables with the field names I've given:

tblPeople
Individual_ID (primary key, autonumber field)
Individual_FN
Individual_LN

tblEmergencyContacts
Contact_ID (autonumber, primary key)
Individual_ID (number field)
ContactRelationship
ContactPhone
ContactFN
ContactLN

in the table tblPeople put the following two items

Mickey Mouse
Donald Duck

in the tblEmergencyContacts put the following two items

Individual_ID 1
ContactRelationship Wife
Contact Phone 111-111-1111
ContactFN Minnie
ContactLN Mouse

Individual_ID 3
ContactRelationship Sister
ContactPhone 222-222-2222
ContactFN Tinker
ContactLN Belle

Now run each of these three queries to see the difference between a LEFT, RIGHT and INNER join are.

Example Query1 (left join)

SELECT tblPeople.Individual_ID, tblPeople.IndividualFN, tblPeople.IndividualLN, tblEmergencyContacts.Contact_ID, tblEmergencyContacts.ContactRelationship, tblEmergencyContacts.ContactPhone
FROM tblPeople LEFT JOIN tblEmergencyContacts ON tblPeople.Individual_ID = tblEmergencyContacts.Individual_ID;

Example Query2 (inner join)

SELECT tblPeople.Individual_ID, tblPeople.IndividualFN, tblPeople.IndividualLN, tblEmergencyContacts.Contact_ID, tblEmergencyContacts.ContactRelationship, tblEmergencyContacts.ContactPhone
FROM tblPeople INNER JOIN tblEmergencyContacts ON tblPeople.Individual_ID = tblEmergencyContacts.Individual_ID;

Example Query3 (right join)

SELECT tblPeople.Individual_ID, tblPeople.IndividualFN, tblPeople.IndividualLN, tblEmergencyContacts.Contact_ID, tblEmergencyContacts.ContactRelationship, tblEmergencyContacts.ContactPhone
FROM tblPeople RIGHT JOIN tblEmergencyContacts ON tblPeople.Individual_ID = tblEmergencyContacts.Individual_ID;

You can see if you run them that you can get radically different results based on your join type.