Re: SQL for Counting 1 total of 4 fields

Tech-Archive recommends: Speed Up your PC by fixing your registry

You need to normalize your table structure so you don't have four "list"
groups. It isn't clear what is stored in these fields compared with the
IndSmallgroup field.

You can use a union query to normalize your structure. I would need a few
sample records rather than a long winded description to help me figure out
what you have and what you need.

--
Duane Hookom
MS Access MVP
--

"Chris Fillar" <ChrisFillar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:613412D3-8DDF-4691-A868-11963763104C@xxxxxxxxxxxxxxxx
Counting 1 total of 4 fields (not summing). I'm not sure how to set up
the
SQL for the query I need.
I have 2 tables:
1. [St Stephen's Small Groups] including fields: Size, IndSmallGroup
(the
key field), City, Meeting Day
2. People including LastName, FirstName, IndSmallgroup, accessadd_list2,
accessadd_list3, accessadd_list4.

I'm trying to count the total number of members in each [St Stephen's
Small
Groups].IndSmallGroup (Each person in People could be in up to 4 small
groups, where the [St Stephen's Small Group].IndSmallGroup text value
would
be in the fields IndSmallgroup, accessadd_list2, accessadd_list3, or
accessadd_list4.)

I then want to use that information in 2 reports
1. Grouped on Meeting Day, showing the total number of members(Size),
LastName, FirstName
2. Grouped on City, showing the total number of members(Size), LastName,
FirstName

I don't have to use the field Size to show the total number of members, I
just didn't know how to count by IndSmallGroup, then do the reports by
Meeting Day and City

If this helps, here's the SQL for another report in the same database:

SELECT [St Stephen's Small Groups].[Open], [St Stephen's Small
Groups].[Community], [St Stephen's Small Groups].[Ages], [St Stephen's
Small
Groups].[Gender], [St Stephen's Small Groups].[Marital Status], [St
Stephen's
Small Groups].[Children Welcome], [St Stephen's Small Groups].[Meeting
Day],
[St Stephen's Small Groups].[Meeting Time], [St Stephen's Small
Groups].[Meeting Frequency], [St Stephen's Small Groups].[Leader Name],
[St
Stephen's Small Groups].[Host City], [St Stephen's Small
Groups].[IndSmallgroup] AS [St Stephen's Small Groups_IndSmallgroup],
[People].[LastName], [People].[FirstName], [People].[MemberStatus],
[People].[E-mailEmailAddr], [People].[HomePhone], [People].[MobilePhone],
[People].[accessadd_list2], [People].[IndSmallgroup] AS
People_IndSmallgroup
FROM [St Stephen's Small Groups] INNER JOIN People ON [St Stephen's Small
Groups].[IndSmallgroup]=[People].[IndSmallgroup] Or [St Stephen's Small
Groups].[IndSmallgroup]=[People].[accessadd_list2] Or [St Stephen's Small
Groups].[IndSmallgroup]=[People].[accessadd_list3] Or [St Stephen's Small
Groups].[IndSmallgroup]=[People].[accessadd_list4];

Thanks for your help,
Chris

--
Chris F


.