Reporting with multiple queries

[Arlo Gueswith]

I've inherited a project from someone in my church and I'm trying to put it in Access 2003. Records contain individual names along with a True/False indicator for each of about 30 different church activites. I want to produce a report for each activity, indicating which names are participating in that activity. Names usually participate in more than one activity. (But I have only one record for each name.)
I don't know SQL. Can someone point me in the write direction?
--arlo

[nico5038]

OK, let's assume you have a table named tblMember with the fields:

MemberName
Activity1
Activity2
Activity3

Now we can build a UNION query like:

select Membername, 1 as Origin, Activity1 from tblMember
UNION
select Membername, 2 as Origin, Activity2 from tblMember
UNION
select Membername, 3 as Origin, Activity3 from tblMember;

The Origin field can be used to distinguish between the activities and could also be a text field with the name of the activity.
On this field you can filter to get the correct members per activity listed.

Getting the idea ?

Nic;o)

[NeoPa]

For each activity that you want to report on your query can be of the form :

SELECT MemberName
FROM tblMember
WHERE (Activityn)

Where Activityn selects the activity you want.

[Arlo Gueswith]

OK, let's assume you have a table named tblMember with the fields:

MemberName
Activity1
Activity2
Activity3

Now we can build a UNION query like:

select Membername, 1 as Origin, Activity1 from tblMember
UNION
select Membername, 2 as Origin, Activity2 from tblMember
UNION
select Membername, 3 as Origin, Activity3 from tblMember;

The Origin field can be used to distinguish between the activities and could also be a text field with the name of the activity.
On this field you can filter to get the correct members per activity listed.

Getting the idea ?

Nic;o)

Thank you, Nic;o. I'm a real SQL newbie and I'm afraid I don't know enough SQL to grasp what looks like a straight-forward approach. If it's not too much trouble, could you explain what your suggested 'select' statements are doing. The "n as Origin" is throwing me.
--arlo

[Arlo Gueswith]

For each activity that you want to report on your query can be of the form :

SELECT MemberName
FROM tblMember
WHERE (Activityn)

Where Activityn selects the activity you want.

Thank you. As you realize by now, I'm a rank beginner and I really appreciate this site. What I'm trying to do is generate a single report that will include all activities. I don't want to run a different report for each of 30 activities. What steps would I add to your approach to generate the complete report?
--arlo

[NeoPa]

In that case Nico's approach is perfect for you.
Try it out and you'll probably get what it's doing for you.
If not, come back explaining what you've got and what you need to understand better.

[nico5038]

Your table is what we call "not normalized".
The "proper" setup would have been a table with for each activity a row like:
MrX Fishes Yes
MrX Smokes Yes
MrX IsEmployed No
MrY Fishes No
MrY Smokes No
MrY IsEmployed No
etc.
In a report you can group now on the activity and filter for Yes or No in the query as you please.

Just take my sample UNION query and replace the fieldnames and the tablename with yours.
Also make sure that the "1 as Origin" is changed into a meaningfull description like:

select membername, "Fishes" as Origin, FishInd from tblX
etc.

The SQL you can enter in the query editor. Just open a new query and don't select a table. Now top left a SQL button will appear and there you can enter the needed select/union statements.

Let me know when and where it goes wrong and publish the SQL with the message here.

Nic;o)