By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,283 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,283 IT Pros & Developers. It's quick & easy.

Reporting with multiple queries

P: 3
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
Nov 30 '06 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Nov 30 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
For each activity that you want to report on your query can be of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT MemberName
  2. FROM tblMember
  3. WHERE (Activityn)
Where Activityn selects the activity you want.
Dec 1 '06 #3

P: 3
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
Dec 2 '06 #4

P: 3
For each activity that you want to report on your query can be of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT MemberName
  2. FROM tblMember
  3. 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
Dec 2 '06 #5

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 2 '06 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 2 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.