Access MS Access 2007 - querying several tables

[fibreperson]

Hi, I hope someone can help. I am a very newbie. I have a table, that was imported from Meditech (DOS based), to Excel and then into Access 2007. I have a list of people, senority date and senority hours, then a separate table of work sites, another table of job codes and classifications. I have made separate tables for 12 meetings and have "looked up" people who attended these meetings. NOW I need to query how many meetings each person attended, they mush attend over 50%. I have over 1100 people on the main list and usually 10-35 people attend each meeting. Can someone help?
Thank you Donna

 

[floppybootstomp]

Hi there very newbie

It's good to eat lots of fibre so I'm glad you're a fibre concious person.

Happy New Year.

Please ignore me, I'm intoxicated

And wait for a sensible answer.

Sometimes I get like this......

 

[PeterDavis]

Meetings

. I have a list of people
I have made separate tables for 12 meetings
and have "looked up" people who attended these meetings.
NOW I need to query how many meetings each person attended,
Thank you Donna

Without knowing the full facts, I should say that you have made a mistake in having 12 separate meeting tables.

You need:

1) A table of people;
2) A table of meetings (you can flag different types of meeting if required)

3) A table of attendances. This links 1 & 2.

So, the attendance table will have 2 foreign keys linked to tables 1 and 2

 

[fibreperson]

need to know # of meeting people attended.

Peter

Thank you for your answer. I have a table of people and I can make a table of meetings, then how do I get my list of who attended which meeting? and how to I find out the total # of meetings they attended. My table of people is over 1100, my table of meetings will be 12. Do I so a query to get my answer? Sorry for so many questions, but I am really new at this and I confuse myself all the time by looking too big.

Donna

 

[PeterDavis]

Group BY

the attendance table will have 2 foreign keys linked to tables 1 and 2

AttID fkPerID fkMeetID
1 1 5
2 1 3
3 3 9
4 3 7
5 3 8
Your table will look like the above. Every time a person attends, you put the person's ID and the meeting id in the table. You will have to do this from your existing data using update queries.

Person 1 went to 2 meetings, person 3 went to 3 meetings

You need a query which counts this for you:

SELECT fkPerId, Count(fkPerId) AS [NO]
FROM TblAttendance
GROUP BY TblAttendance.fkPerId;

Copy this into the SQL view of the query design grid

(seems I can't post a table here-formatting is lost)

 

[fibreperson]

Peter
Thank you so much for your help. I have it working. It has taken me all day to get it showing the way I want it too, but it works great, it was my stuff causing the problems. Thanks again.

Donna