SQL for Counting 1 total of 4 fields

[Guest]

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

 

[Duane Hookom]

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.

 

[Guest]

Duane,
Thank you for your speedy reply!

Since I’m new, I’m not sure what you mean by normalizing the table and I’m
not sure of the format to send you the records. Here’s an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host Address1 Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks Open Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly Ron A/ Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3 ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database system, of
which I have little or no control (not Access), called ACS. Each person in
People has their own record, but ACS cannot export any discrete number/ID
field for each person. Also, ACS is unable to track and provide reports for
small group membership and small group makeup (type of group, when it meets,
etc.) , so I was asked to provide help via Access (although I’m just a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the basic
information about the makeup of each small group, except who is in the
group, and I set up the one-to-many relationship between [St Stephen's Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the each
small group. Since each person in the People table can be a member in up to
4 small groups, the “People†fields Indsmallgroup, access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a small group
that the person is in. For example John Doe is in Smith 1 and Benson, so for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every person(record)
exported from ACS to the People table is in atleast 1 group, so every record
has Indsmallgroup filled with the applicable group name(right now there are
less than 50 groups and each group has a distinctive, discreet name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F

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
--

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

 

[tina]

re normalization: see http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth

Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the table and I'm
not sure of the format to send you the records. Here's an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host Address1 Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks Open Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly Ron A/ Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus

E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4

Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database system, of
which I have little or no control (not Access), called ACS. Each person in
People has their own record, but ACS cannot export any discrete number/ID
field for each person. Also, ACS is unable to track and provide reports for
small group membership and small group makeup (type of group, when it meets,
etc.) , so I was asked to provide help via Access (although I'm just a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the basic
information about the makeup of each small group, except who is in the
group, and I set up the one-to-many relationship between [St Stephen's Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the each
small group. Since each person in the People table can be a member in up to
4 small groups, the "People" fields Indsmallgroup, access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a small group
that the person is in. For example John Doe is in Smith 1 and Benson, so for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every person(record)
exported from ACS to the People table is in atleast 1 group, so every record
has Indsmallgroup filled with the applicable group name(right now there are
less than 50 groups and each group has a distinctive, discreet name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F

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
--

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

 

[Guest]

Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields. Can you
help with the SQL counting with the tables as they are or what do I need to
do?
Thanks
--
Chris F

re normalization: see http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth

Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the table and I'm
not sure of the format to send you the records. Here's an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host Address1 Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks Open Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly Ron A/ Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus

E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4

Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database system, of
which I have little or no control (not Access), called ACS. Each person in
People has their own record, but ACS cannot export any discrete number/ID
field for each person. Also, ACS is unable to track and provide reports for
small group membership and small group makeup (type of group, when it meets,
etc.) , so I was asked to provide help via Access (although I'm just a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the basic
information about the makeup of each small group, except who is in the
group, and I set up the one-to-many relationship between [St Stephen's Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the each
small group. Since each person in the People table can be a member in up to
4 small groups, the "People" fields Indsmallgroup, access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a small group
that the person is in. For example John Doe is in Smith 1 and Benson, so for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every person(record)
exported from ACS to the People table is in atleast 1 group, so every record
has Indsmallgroup filled with the applicable group name(right now there are
less than 50 groups and each group has a distinctive, discreet name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F

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
--

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

 

[Duane Hookom]

Apparently you have a record for a person with 4 different fields that might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a member of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields. Can you
help with the SQL counting with the tables as they are or what do I need
to
do?
Thanks
--
Chris F

re normalization: see http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth

Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the table and
I'm
not sure of the format to send you the records. Here's an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host Address1 Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks Open Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly Ron
A/ Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus

E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4

Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database system,
of
which I have little or no control (not Access), called ACS. Each
person in
People has their own record, but ACS cannot export any discrete
number/ID
field for each person. Also, ACS is unable to track and provide
reports for
small group membership and small group makeup (type of group, when it meets,
etc.) , so I was asked to provide help via Access (although I'm just a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the
basic
information about the makeup of each small group, except who is in the
group, and I set up the one-to-many relationship between [St Stephen's Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the
each
small group. Since each person in the People table can be a member in
up to
4 small groups, the "People" fields Indsmallgroup, access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a small group
that the person is in. For example John Doe is in Smith 1 and Benson,
so for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every person(record)
exported from ACS to the People table is in atleast 1 group, so every record
has Indsmallgroup filled with the applicable group name(right now there are
less than 50 groups and each group has a distinctive, discreet name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

message
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

 

[Guest]

Duane,
You are correct in how the table People is set up. Unfortunately, I am
stuck with how People is set up because ACS (the database system where the
information in maintained and then exported) cannot handle multiple records
for 1 individual. Another issue is when personal information is changed,
whether it is phone number, email or membership in a group; there is no
unique id number for the record, I have to delete all the data in the table
and paste in the new data.

As far as the Junction table, is that something I can generate from the
current People table and can it be automated for when the People data is
updated?

I certainly understand your example, but I still find normalization a tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F

Apparently you have a record for a person with 4 different fields that might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a member of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields. Can you
help with the SQL counting with the tables as they are or what do I need
to
do?
Thanks
--
Chris F

re normalization: see http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the table and
I'm
not sure of the format to send you the records. Here's an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database system,
of
which I have little or no control (not Access), called ACS. Each
person
in
People has their own record, but ACS cannot export any discrete
number/ID
field for each person. Also, ACS is unable to track and provide
reports
for
small group membership and small group makeup (type of group, when it
meets,
etc.) , so I was asked to provide help via Access (although I'm just a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the
basic
information about the makeup of each small group, except who is in the
group, and I set up the one-to-many relationship between [St Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the
each
small group. Since each person in the People table can be a member in
up
to
4 small groups, the "People" fields Indsmallgroup, access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a small
group
that the person is in. For example John Doe is in Smith 1 and Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every person(record)
exported from ACS to the People table is in atleast 1 group, so every
record
has Indsmallgroup filled with the applicable group name(right now there
are
less than 50 groups and each group has a distinctive, discreet name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

message
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

 

[Duane Hookom]

If you can't create a junction table, you should be able to create a union
query from your "people" table. For instance if your people table had these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

Duane,
You are correct in how the table People is set up. Unfortunately, I am
stuck with how People is set up because ACS (the database system where the
information in maintained and then exported) cannot handle multiple
records
for 1 individual. Another issue is when personal information is changed,
whether it is phone number, email or membership in a group; there is no
unique id number for the record, I have to delete all the data in the
table
and paste in the new data.

As far as the Junction table, is that something I can generate from the
current People table and can it be automated for when the People data is
updated?

I certainly understand your example, but I still find normalization a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F

Apparently you have a record for a person with 4 different fields that
might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a member of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields. Can
you
help with the SQL counting with the tables as they are or what do I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the table
and
I'm
not sure of the format to send you the records. Here's an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database
system,
of
which I have little or no control (not Access), called ACS. Each
person
in
People has their own record, but ACS cannot export any discrete
number/ID
field for each person. Also, ACS is unable to track and provide
reports
for
small group membership and small group makeup (type of group, when
it
meets,
etc.) , so I was asked to provide help via Access (although I'm just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the
basic
information about the makeup of each small group, except who is in
the
group, and I set up the one-to-many relationship between [St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the
each
small group. Since each person in the People table can be a member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a
small
group
that the person is in. For example John Doe is in Smith 1 and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1 group, so
every
record
has Indsmallgroup filled with the applicable group name(right now
there
are
less than 50 groups and each group has a distinctive, discreet
name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

message
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

 

[Guest]

Duane,
I’m sorry I haven’t answered you before, but I’ve been trying to get the ACS
person to export the PersonID field into tblPeople without success. I should
get the PersonID in a week. In the mean time, Is it possible to run the
union query by record or some other way, until I get a PersonID? I’ve been
trying that without success.
Thanks again,
Chris
--
Chris F

If you can't create a junction table, you should be able to create a union
query from your "people" table. For instance if your people table had these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

Duane,
You are correct in how the table People is set up. Unfortunately, I am
stuck with how People is set up because ACS (the database system where the
information in maintained and then exported) cannot handle multiple
records
for 1 individual. Another issue is when personal information is changed,
whether it is phone number, email or membership in a group; there is no
unique id number for the record, I have to delete all the data in the
table
and paste in the new data.

As far as the Junction table, is that something I can generate from the
current People table and can it be automated for when the People data is
updated?

I certainly understand your example, but I still find normalization a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F

Apparently you have a record for a person with 4 different fields that
might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a member of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields. Can
you
help with the SQL counting with the tables as they are or what do I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the table
and
I'm
not sure of the format to send you the records. Here's an attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed) Ull

Background:
The table People is exported to Access, from another Database
system,
of
which I have little or no control (not Access), called ACS. Each
person
in
People has their own record, but ACS cannot export any discrete
number/ID
field for each person. Also, ACS is unable to track and provide
reports
for
small group membership and small group makeup (type of group, when
it
meets,
etc.) , so I was asked to provide help via Access (although I'm just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain all the
basic
information about the makeup of each small group, except who is in
the
group, and I set up the one-to-many relationship between [St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of the
each
small group. Since each person in the People table can be a member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a
small
group
that the person is in. For example John Doe is in Smith 1 and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1 group, so
every
record
has Indsmallgroup filled with the applicable group name(right now
there
are
less than 50 groups and each group has a distinctive, discreet
name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

message
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

 

[Duane Hookom]

I don't recall what you have right now. Again, you can use a union query to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

Duane,
I'm sorry I haven't answered you before, but I've been trying to get the
ACS
person to export the PersonID field into tblPeople without success. I
should
get the PersonID in a week. In the mean time, Is it possible to run the
union query by record or some other way, until I get a PersonID? I've
been
trying that without success.
Thanks again,
Chris
--
Chris F

If you can't create a junction table, you should be able to create a
union
query from your "people" table. For instance if your people table had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

Duane,
You are correct in how the table People is set up. Unfortunately, I am
stuck with how People is set up because ACS (the database system where
the
information in maintained and then exported) cannot handle multiple
records
for 1 individual. Another issue is when personal information is
changed,
whether it is phone number, email or membership in a group; there is no
unique id number for the record, I have to delete all the data in the
table
and paste in the new data.

As far as the Junction table, is that something I can generate from the
current People table and can it be automated for when the People data
is
updated?

I certainly understand your example, but I still find normalization a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different fields that
might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

message
Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields.
Can
you
help with the SQL counting with the tables as they are or what do I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the
table
and
I'm
not sure of the format to send you the records. Here's an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed)
Ull

Background:
The table People is exported to Access, from another Database
system,
of
which I have little or no control (not Access), called ACS. Each
person
in
People has their own record, but ACS cannot export any discrete
number/ID
field for each person. Also, ACS is unable to track and provide
reports
for
small group membership and small group makeup (type of group,
when
it
meets,
etc.) , so I was asked to provide help via Access (although I'm
just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain all
the
basic
information about the makeup of each small group, except who is
in
the
group, and I set up the one-to-many relationship between [St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of
the
each
small group. Since each person in the People table can be a
member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a
small
group
that the person is in. For example John Doe is in Smith 1 and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1 group, so
every
record
has Indsmallgroup filled with the applicable group name(right now
there
are
less than 50 groups and each group has a distinctive, discreet
name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

message
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

 

[Guest]

Duane,
I'm not sure how to set it up. This was my attempt to normalize an existing
query [GroupLocator], but obviously I have something wrong. Do I need an ID
field? Once the data is normalized, I want to count the number people in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4), but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName], [IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F

I don't recall what you have right now. Again, you can use a union query to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

Duane,
I'm sorry I haven't answered you before, but I've been trying to get the
ACS
person to export the PersonID field into tblPeople without success. I
should
get the PersonID in a week. In the mean time, Is it possible to run the
union query by record or some other way, until I get a PersonID? I've
been
trying that without success.
Thanks again,
Chris
--
Chris F

If you can't create a junction table, you should be able to create a
union
query from your "people" table. For instance if your people table had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

Duane,
You are correct in how the table People is set up. Unfortunately, I am
stuck with how People is set up because ACS (the database system where
the
information in maintained and then exported) cannot handle multiple
records
for 1 individual. Another issue is when personal information is
changed,
whether it is phone number, email or membership in a group; there is no
unique id number for the record, I have to delete all the data in the
table
and paste in the new data.

As far as the Junction table, is that something I can generate from the
current People table and can it be automated for when the People data
is
updated?

I certainly understand your example, but I still find normalization a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different fields that
might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

message
Duane,
I read the reference, but I still do not understand what you mean by
normalization in this case, unless you want me to rename fields.
Can
you
help with the SQL counting with the tables as they are or what do I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the
table
and
I'm
not sure of the format to send you the records. Here's an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275 Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed)
Ull

Background:
The table People is exported to Access, from another Database
system,
of
which I have little or no control (not Access), called ACS. Each
person
in
People has their own record, but ACS cannot export any discrete
number/ID
field for each person. Also, ACS is unable to track and provide
reports
for
small group membership and small group makeup (type of group,
when
it
meets,
etc.) , so I was asked to provide help via Access (although I'm
just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain all
the
basic
information about the makeup of each small group, except who is
in
the
group, and I set up the one-to-many relationship between [St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name of
the
each
small group. Since each person in the People table can be a
member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of a
small
group
that the person is in. For example John Doe is in Smith 1 and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1 group, so
every
record
has Indsmallgroup filled with the applicable group name(right now
there
are
less than 50 groups and each group has a distinctive, discreet
name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

message
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),

 

[Duane Hookom]

Go back and notice how the number of fields in each "SELECT" in my union
query was the same. Also, my SELECTs included a primary key field.
--
Duane Hookom
MS Access MVP

Duane,
I'm not sure how to set it up. This was my attempt to normalize an
existing
query [GroupLocator], but obviously I have something wrong. Do I need an
ID
field? Once the data is normalized, I want to count the number people in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4), but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName], [IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F

I don't recall what you have right now. Again, you can use a union query
to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

Duane,
I'm sorry I haven't answered you before, but I've been trying to get
the
ACS
person to export the PersonID field into tblPeople without success. I
should
get the PersonID in a week. In the mean time, Is it possible to run
the
union query by record or some other way, until I get a PersonID? I've
been
trying that without success.
Thanks again,
Chris
--
Chris F


:

If you can't create a junction table, you should be able to create a
union
query from your "people" table. For instance if your people table had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

message
Duane,
You are correct in how the table People is set up. Unfortunately, I
am
stuck with how People is set up because ACS (the database system
where
the
information in maintained and then exported) cannot handle multiple
records
for 1 individual. Another issue is when personal information is
changed,
whether it is phone number, email or membership in a group; there is
no
unique id number for the record, I have to delete all the data in
the
table
and paste in the new data.

As far as the Junction table, is that something I can generate from
the
current People table and can it be automated for when the People
data
is
updated?

I certainly understand your example, but I still find normalization
a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different fields
that
might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would
contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a
member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

message
Duane,
I read the reference, but I still do not understand what you mean
by
normalization in this case, unless you want me to rename fields.
Can
you
help with the SQL counting with the tables as they are or what do
I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the
table
and
I'm
not sure of the format to send you the records. Here's an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open
Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM
Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275
Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed)
Ull

Background:
The table People is exported to Access, from another Database
system,
of
which I have little or no control (not Access), called ACS.
Each
person
in
People has their own record, but ACS cannot export any
discrete
number/ID
field for each person. Also, ACS is unable to track and
provide
reports
for
small group membership and small group makeup (type of group,
when
it
meets,
etc.) , so I was asked to provide help via Access (although
I'm
just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain
all
the
basic
information about the makeup of each small group, except who
is
in
the
group, and I set up the one-to-many relationship between [St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name
of
the
each
small group. Since each person in the People table can be a
member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of
a
small
group
that the person is in. For example John Doe is in Smith 1 and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1 group,
so
every
record
has Indsmallgroup filled with the applicable group name(right
now
there
are
less than 50 groups and each group has a distinctive, discreet
name).

I hope you this information helps you to help me.
Thanks,
Chris

--
Chris F


:

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
--

in
message
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),

 

[Guest]

Duane,
Thanks! It worked. I had an existing problem (that I hadn’t noticed before
your comments) in that the ACS-exported table “Peopleâ€, did not have a
suitable field that could serve as a key field, so I added a autonumber
field, TempID, and made TempID the key field (the ACS-exported table “Peopleâ€
will have a field added in the future, when I can get that person to do it,
then I will use that field as the key field).

I had a little trouble with “as Group†in your first suggested line of code:
“SELECT PersonID, Group1 as Groupâ€. I tried creating a field “Group†and
other options, but everytime I included the “as Group†in the first line of
code, I got the error: “The SELECT statement includes a reserved word or an
argument name that is misspelled or missing, or the punctuation is
incorrect.†I’m sure I was doing something wrong, but I didn’t know what it
was. Anyway, with your help I’ve now taken the first step, and have a
successful union query.

What I’d like to do next, is create a report using this union query that
I’ve named qryNormalized, with another table named [St Stephen's Small
Groups], and sort on the field [St Stephen's Small Groups].[City]; but still
be able to count and display the number of people in each group from
qryNormalized. Whenever I try to combine the fields from the query and the
table I get the error: “You have chosen fields from record sources which the
wizard can’t connect. You may have chosen fields from a table and a query on
that table. If so, try choosing fields from only the table or only the
query.†The query is based on the table People and not [St Stephen's Small
Groups], but the 2 Tables are linked by the field IndSmallGroup. Can I get
there from here?

Thanks again,
Chris


--
Chris F

Go back and notice how the number of fields in each "SELECT" in my union
query was the same. Also, my SELECTs included a primary key field.
--
Duane Hookom
MS Access MVP

Duane,
I'm not sure how to set it up. This was my attempt to normalize an
existing
query [GroupLocator], but obviously I have something wrong. Do I need an
ID
field? Once the data is normalized, I want to count the number people in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4), but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName], [IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F

I don't recall what you have right now. Again, you can use a union query
to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

Duane,
I'm sorry I haven't answered you before, but I've been trying to get
the
ACS
person to export the PersonID field into tblPeople without success. I
should
get the PersonID in a week. In the mean time, Is it possible to run
the
union query by record or some other way, until I get a PersonID? I've
been
trying that without success.
Thanks again,
Chris
--
Chris F


:

If you can't create a junction table, you should be able to create a
union
query from your "people" table. For instance if your people table had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

message
Duane,
You are correct in how the table People is set up. Unfortunately, I
am
stuck with how People is set up because ACS (the database system
where
the
information in maintained and then exported) cannot handle multiple
records
for 1 individual. Another issue is when personal information is
changed,
whether it is phone number, email or membership in a group; there is
no
unique id number for the record, I have to delete all the data in
the
table
and paste in the new data.

As far as the Junction table, is that something I can generate from
the
current People table and can it be automated for when the People
data
is
updated?

I certainly understand your example, but I still find normalization
a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different fields
that
might
or might not contain the name of a small group. If so, this is not
normalized. I would expect to see a JUNCTION table which would
contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a
member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

message
Duane,
I read the reference, but I still do not understand what you mean
by
normalization in this case, unless you want me to rename fields.
Can
you
help with the SQL counting with the tables as they are or what do
I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing the
table
and
I'm
not sure of the format to send you the records. Here's an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open
Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM
Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275
Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member (e-mail address removed)
Ull

Background:
The table People is exported to Access, from another Database
system,
of
which I have little or no control (not Access), called ACS.
Each
person
in
People has their own record, but ACS cannot export any
discrete
number/ID
field for each person. Also, ACS is unable to track and
provide
reports
for
small group membership and small group makeup (type of group,
when
it
meets,
etc.) , so I was asked to provide help via Access (although
I'm
just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain
all
the
basic
information about the makeup of each small group, except who
is
in
the
group, and I set up the one-to-many relationship between [St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the name
of
the
each
small group. Since each person in the People table can be a
member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name of
a
small
group
that the person is in. For example John Doe is in Smith 1 and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1 group,
so
every

 

[Duane Hookom]

What fields do you currently have in your table/queries and how do you want
to display them in a report?
--
Duane Hookom
MS Access MVP

Duane,
Thanks! It worked. I had an existing problem (that I hadn't noticed
before
your comments) in that the ACS-exported table "People", did not have a
suitable field that could serve as a key field, so I added a autonumber
field, TempID, and made TempID the key field (the ACS-exported table
"People"
will have a field added in the future, when I can get that person to do
it,
then I will use that field as the key field).

I had a little trouble with "as Group" in your first suggested line of
code:
"SELECT PersonID, Group1 as Group". I tried creating a field "Group" and
other options, but everytime I included the "as Group" in the first line
of
code, I got the error: "The SELECT statement includes a reserved word or
an
argument name that is misspelled or missing, or the punctuation is
incorrect." I'm sure I was doing something wrong, but I didn't know what
it
was. Anyway, with your help I've now taken the first step, and have a
successful union query.

What I'd like to do next, is create a report using this union query that
I've named qryNormalized, with another table named [St Stephen's Small
Groups], and sort on the field [St Stephen's Small Groups].[City]; but
still
be able to count and display the number of people in each group from
qryNormalized. Whenever I try to combine the fields from the query and
the
table I get the error: "You have chosen fields from record sources which
the
wizard can't connect. You may have chosen fields from a table and a query
on
that table. If so, try choosing fields from only the table or only the
query." The query is based on the table People and not [St Stephen's
Small
Groups], but the 2 Tables are linked by the field IndSmallGroup. Can I
get
there from here?

Thanks again,
Chris


--
Chris F

Go back and notice how the number of fields in each "SELECT" in my union
query was the same. Also, my SELECTs included a primary key field.
--
Duane Hookom
MS Access MVP

Duane,
I'm not sure how to set it up. This was my attempt to normalize an
existing
query [GroupLocator], but obviously I have something wrong. Do I need
an
ID
field? Once the data is normalized, I want to count the number people
in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4),
but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName], [IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F


:

I don't recall what you have right now. Again, you can use a union
query
to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

message
Duane,
I'm sorry I haven't answered you before, but I've been trying to get
the
ACS
person to export the PersonID field into tblPeople without success.
I
should
get the PersonID in a week. In the mean time, Is it possible to run
the
union query by record or some other way, until I get a PersonID?
I've
been
trying that without success.
Thanks again,
Chris
--
Chris F


:

If you can't create a junction table, you should be able to create
a
union
query from your "people" table. For instance if your people table
had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

message
Duane,
You are correct in how the table People is set up.
Unfortunately, I
am
stuck with how People is set up because ACS (the database system
where
the
information in maintained and then exported) cannot handle
multiple
records
for 1 individual. Another issue is when personal information is
changed,
whether it is phone number, email or membership in a group; there
is
no
unique id number for the record, I have to delete all the data in
the
table
and paste in the new data.

As far as the Junction table, is that something I can generate
from
the
current People table and can it be automated for when the People
data
is
updated?

I certainly understand your example, but I still find
normalization
a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different
fields
that
might
or might not contain the name of a small group. If so, this is
not
normalized. I would expect to see a JUNCTION table which would
contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a
member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

message
Duane,
I read the reference, but I still do not understand what you
mean
by
normalization in this case, unless you want me to rename
fields.
Can
you
help with the SQL counting with the tables as they are or what
do
I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


in
message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing
the
table
and
I'm
not sure of the format to send you the records. Here's an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status
Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name
Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open
Remarks
Open
Remarks
Date Community DateModified TimeModified Size
57 0 Adams/Benson Mixed Mixed Mixed No Thursday 7 PM -9 PM
Weekly
Ron
A/
Ron
B Panara Bread - Robinson 250 Robinson, Pittsburgh PA 15275
Open

From People (I deleted some fields to fit it on this page):
LastName FirstName Address1 City State ZIPCode MemberStatus
E-mailEmailAddr IndSmallgroup ACCESSADD_List2 ACCESSADD_List3
ACCESSADD_List4
Ull Richard 5 Little
Rd Sewickley PA 15143 Member (e-mail address removed) Ull
Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member
(e-mail address removed)
Ull

Background:
The table People is exported to Access, from another
Database
system,
of
which I have little or no control (not Access), called ACS.
Each
person
in
People has their own record, but ACS cannot export any
discrete
number/ID
field for each person. Also, ACS is unable to track and
provide
reports
for
small group membership and small group makeup (type of
group,
when
it
meets,
etc.) , so I was asked to provide help via Access (although
I'm
just
a
beginner).

I created the Table [St Stephen's Small Groups], to contain
all
the
basic
information about the makeup of each small group, except
who
is
in
the
group, and I set up the one-to-many relationship between
[St
Stephen's
Small
Groups].[IndSmallgroup] and People.IndSmallgroup.

[St Stephen's Small Groups].[IndSmallgroup] contains the
name
of
the
each
small group. Since each person in the People table can be
a
member
in
up
to
4 small groups, the "People" fields Indsmallgroup,
access¬¬_addlist2,
access_addlist3, and access_addlist 4 all contain the name
of
a
small
group
that the person is in. For example John Doe is in Smith 1
and
Benson,
so
for
John Doe, Indsmallgroup=[Smith 1] and
access_addlist2=[Benson]
(access_addlist3 and access_addlist4 are blank). Every
person(record)
exported from ACS to the People table is in atleast 1
group,
so
every

 

[Guest]

Duane,

Here are the fields I wanted for the report:

[St Stephen's Small Groups].IndSmallgroup, [St Stephen's Small Groups].[Host
City], [St Stephen's Small Groups].[Leader Name], [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], WeekdaySequence.Sequence,
WeekdaySequence.Weekday

Additionally, I wanted to count and display the number of
people(IndSmallGroup) in each group from the union query, qryNormalized.

I actually wanted several reports of the same information, but grouped on
different fields. The first report grouped by [Host City].

Another report grouped on [Meeting Day]. For this I thought I needed
another related table so I could get the results by day of the week, i.e.
Monday, Tuesday…; rather than a to z resuts of Friday, Monday… (I used
“INNER JOIN [St Stephen's Small Groups] ON WeekdaySequence.Weekday = [St
Stephen's Small Groups].[Meeting Day]†where WeekdaySequence would equate
Monday=1, Tuesday=2… )

Thanks,
Chris

--
Chris F

What fields do you currently have in your table/queries and how do you want
to display them in a report?
--
Duane Hookom
MS Access MVP

Duane,
Thanks! It worked. I had an existing problem (that I hadn't noticed
before
your comments) in that the ACS-exported table "People", did not have a
suitable field that could serve as a key field, so I added a autonumber
field, TempID, and made TempID the key field (the ACS-exported table
"People"
will have a field added in the future, when I can get that person to do
it,
then I will use that field as the key field).

I had a little trouble with "as Group" in your first suggested line of
code:
"SELECT PersonID, Group1 as Group". I tried creating a field "Group" and
other options, but everytime I included the "as Group" in the first line
of
code, I got the error: "The SELECT statement includes a reserved word or
an
argument name that is misspelled or missing, or the punctuation is
incorrect." I'm sure I was doing something wrong, but I didn't know what
it
was. Anyway, with your help I've now taken the first step, and have a
successful union query.

What I'd like to do next, is create a report using this union query that
I've named qryNormalized, with another table named [St Stephen's Small
Groups], and sort on the field [St Stephen's Small Groups].[City]; but
still
be able to count and display the number of people in each group from
qryNormalized. Whenever I try to combine the fields from the query and
the
table I get the error: "You have chosen fields from record sources which
the
wizard can't connect. You may have chosen fields from a table and a query
on
that table. If so, try choosing fields from only the table or only the
query." The query is based on the table People and not [St Stephen's
Small
Groups], but the 2 Tables are linked by the field IndSmallGroup. Can I
get
there from here?

Thanks again,
Chris


--
Chris F

Go back and notice how the number of fields in each "SELECT" in my union
query was the same. Also, my SELECTs included a primary key field.
--
Duane Hookom
MS Access MVP

Duane,
I'm not sure how to set it up. This was my attempt to normalize an
existing
query [GroupLocator], but obviously I have something wrong. Do I need
an
ID
field? Once the data is normalized, I want to count the number people
in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4),
but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName], [IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F


:

I don't recall what you have right now. Again, you can use a union
query
to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

message
Duane,
I'm sorry I haven't answered you before, but I've been trying to get
the
ACS
person to export the PersonID field into tblPeople without success.
I
should
get the PersonID in a week. In the mean time, Is it possible to run
the
union query by record or some other way, until I get a PersonID?
I've
been
trying that without success.
Thanks again,
Chris
--
Chris F


:

If you can't create a junction table, you should be able to create
a
union
query from your "people" table. For instance if your people table
had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

message
Duane,
You are correct in how the table People is set up.
Unfortunately, I
am
stuck with how People is set up because ACS (the database system
where
the
information in maintained and then exported) cannot handle
multiple
records
for 1 individual. Another issue is when personal information is
changed,
whether it is phone number, email or membership in a group; there
is
no
unique id number for the record, I have to delete all the data in
the
table
and paste in the new data.

As far as the Junction table, is that something I can generate
from
the
current People table and can it be automated for when the People
data
is
updated?

I certainly understand your example, but I still find
normalization
a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different
fields
that
might
or might not contain the name of a small group. If so, this is
not
normalized. I would expect to see a JUNCTION table which would
contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is a
member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

message
Duane,
I read the reference, but I still do not understand what you
mean
by
normalization in this case, unless you want me to rename
fields.
Can
you
help with the SQL counting with the tables as they are or what
do
I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


in
message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing
the
table
and
I'm
not sure of the format to send you the records. Here's an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status
Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name
Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open
Remarks

 

[Duane Hookom]

I was hoping to "What fields do you currently have in your table/queries and
how do you want to display them in a report?" This would include your union
query output fields. I would expect to see a field that in each query that
is common and can be used for a join. Perhaps you have a primary key in [St
Stephen's Small Groups].

--
Duane Hookom
MS Access MVP

Duane,

Here are the fields I wanted for the report:

[St Stephen's Small Groups].IndSmallgroup, [St Stephen's Small
Groups].[Host
City], [St Stephen's Small Groups].[Leader Name], [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], WeekdaySequence.Sequence,
WeekdaySequence.Weekday

Additionally, I wanted to count and display the number of
people(IndSmallGroup) in each group from the union query, qryNormalized.

I actually wanted several reports of the same information, but grouped on
different fields. The first report grouped by [Host City].

Another report grouped on [Meeting Day]. For this I thought I needed
another related table so I could get the results by day of the week, i.e.
Monday, Tuesday.; rather than a to z resuts of Friday, Monday. (I used
"INNER JOIN [St Stephen's Small Groups] ON WeekdaySequence.Weekday = [St
Stephen's Small Groups].[Meeting Day]" where WeekdaySequence would equate
Monday=1, Tuesday=2. )

Thanks,
Chris

--
Chris F

What fields do you currently have in your table/queries and how do you
want
to display them in a report?
--
Duane Hookom
MS Access MVP

Duane,
Thanks! It worked. I had an existing problem (that I hadn't noticed
before
your comments) in that the ACS-exported table "People", did not have a
suitable field that could serve as a key field, so I added a autonumber
field, TempID, and made TempID the key field (the ACS-exported table
"People"
will have a field added in the future, when I can get that person to do
it,
then I will use that field as the key field).

I had a little trouble with "as Group" in your first suggested line of
code:
"SELECT PersonID, Group1 as Group". I tried creating a field "Group"
and
other options, but everytime I included the "as Group" in the first
line
of
code, I got the error: "The SELECT statement includes a reserved word
or
an
argument name that is misspelled or missing, or the punctuation is
incorrect." I'm sure I was doing something wrong, but I didn't know
what
it
was. Anyway, with your help I've now taken the first step, and have a
successful union query.

What I'd like to do next, is create a report using this union query
that
I've named qryNormalized, with another table named [St Stephen's Small
Groups], and sort on the field [St Stephen's Small Groups].[City]; but
still
be able to count and display the number of people in each group from
qryNormalized. Whenever I try to combine the fields from the query and
the
table I get the error: "You have chosen fields from record sources
which
the
wizard can't connect. You may have chosen fields from a table and a
query
on
that table. If so, try choosing fields from only the table or only the
query." The query is based on the table People and not [St Stephen's
Small
Groups], but the 2 Tables are linked by the field IndSmallGroup. Can I
get
there from here?

Thanks again,
Chris


--
Chris F


:

Go back and notice how the number of fields in each "SELECT" in my
union
query was the same. Also, my SELECTs included a primary key field.
--
Duane Hookom
MS Access MVP

message
Duane,
I'm not sure how to set it up. This was my attempt to normalize an
existing
query [GroupLocator], but obviously I have something wrong. Do I
need
an
ID
field? Once the data is normalized, I want to count the number
people
in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4),
but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName],
[IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F


:

I don't recall what you have right now. Again, you can use a union
query
to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

message
Duane,
I'm sorry I haven't answered you before, but I've been trying to
get
the
ACS
person to export the PersonID field into tblPeople without
success.
I
should
get the PersonID in a week. In the mean time, Is it possible to
run
the
union query by record or some other way, until I get a PersonID?
I've
been
trying that without success.
Thanks again,
Chris
--
Chris F


:

If you can't create a junction table, you should be able to
create
a
union
query from your "people" table. For instance if your people
table
had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

message
Duane,
You are correct in how the table People is set up.
Unfortunately, I
am
stuck with how People is set up because ACS (the database
system
where
the
information in maintained and then exported) cannot handle
multiple
records
for 1 individual. Another issue is when personal information
is
changed,
whether it is phone number, email or membership in a group;
there
is
no
unique id number for the record, I have to delete all the data
in
the
table
and paste in the new data.

As far as the Junction table, is that something I can generate
from
the
current People table and can it be automated for when the
People
data
is
updated?

I certainly understand your example, but I still find
normalization
a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different
fields
that
might
or might not contain the name of a small group. If so, this
is
not
normalized. I would expect to see a JUNCTION table which
would
contain
records for each person that is a member of a group.

If Chris is a member of small groups A, B, and D and Duane is
a
member
of
small groups B,C, and E I would expect to see records like:

Chris A
Chris B
Chris D
Duane B
Duane C
Duane E

Duane Hookom
MS Access MVP

in
message
Duane,
I read the reference, but I still do not understand what
you
mean
by
normalization in this case, unless you want me to rename
fields.
Can
you
help with the SQL counting with the tables as they are or
what
do
I
need
to
do?
Thanks
--
Chris F


:

re normalization: see
http://home.att.net/~california.db/tips.html#aTip1
for more information.

hth


"Chris Fillar" <[email protected]>
wrote
in
message
Duane,
Thank you for your speedy reply!

Since I'm new, I'm not sure what you mean by normalizing
the
table
and
I'm
not sure of the format to send you the records. Here's
an
attempt:
From St Stephen's Small Groups:
ID GroupID IndSmallgroup Ages Gender Marital Status
Children
Welcome
Meeting
Day Meeting Time Meeting Frequency Leader Name Host Name
Host
Address1
Host
Address2 Host City Host State Host Zip Remarks Open Open
Remarks

 

[Guest]

Duane,
I guess I'm a little lost with "I would expect to see a field that in each
query that is common and can be used for a join". I probably don't have the
database set up correctly in that I'm using 3 tables and 3 queries to
generate the 12 reports, but I'm not sure what I should have in the queries.
In any case, it seems to be working. I do have a key field for [St Stephen's
Small Groups], which is IndSmallGroup.
--
Chris F

I was hoping to "What fields do you currently have in your table/queries and
how do you want to display them in a report?" This would include your union
query output fields. I would expect to see a field that in each query that
is common and can be used for a join. Perhaps you have a primary key in [St
Stephen's Small Groups].

--
Duane Hookom
MS Access MVP

Duane,

Here are the fields I wanted for the report:

[St Stephen's Small Groups].IndSmallgroup, [St Stephen's Small
Groups].[Host
City], [St Stephen's Small Groups].[Leader Name], [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], WeekdaySequence.Sequence,
WeekdaySequence.Weekday

Additionally, I wanted to count and display the number of
people(IndSmallGroup) in each group from the union query, qryNormalized.

I actually wanted several reports of the same information, but grouped on
different fields. The first report grouped by [Host City].

Another report grouped on [Meeting Day]. For this I thought I needed
another related table so I could get the results by day of the week, i.e.
Monday, Tuesday.; rather than a to z resuts of Friday, Monday. (I used
"INNER JOIN [St Stephen's Small Groups] ON WeekdaySequence.Weekday = [St
Stephen's Small Groups].[Meeting Day]" where WeekdaySequence would equate
Monday=1, Tuesday=2. )

Thanks,
Chris

--
Chris F

What fields do you currently have in your table/queries and how do you
want
to display them in a report?
--
Duane Hookom
MS Access MVP

Duane,
Thanks! It worked. I had an existing problem (that I hadn't noticed
before
your comments) in that the ACS-exported table "People", did not have a
suitable field that could serve as a key field, so I added a autonumber
field, TempID, and made TempID the key field (the ACS-exported table
"People"
will have a field added in the future, when I can get that person to do
it,
then I will use that field as the key field).

I had a little trouble with "as Group" in your first suggested line of
code:
"SELECT PersonID, Group1 as Group". I tried creating a field "Group"
and
other options, but everytime I included the "as Group" in the first
line
of
code, I got the error: "The SELECT statement includes a reserved word
or
an
argument name that is misspelled or missing, or the punctuation is
incorrect." I'm sure I was doing something wrong, but I didn't know
what
it
was. Anyway, with your help I've now taken the first step, and have a
successful union query.

What I'd like to do next, is create a report using this union query
that
I've named qryNormalized, with another table named [St Stephen's Small
Groups], and sort on the field [St Stephen's Small Groups].[City]; but
still
be able to count and display the number of people in each group from
qryNormalized. Whenever I try to combine the fields from the query and
the
table I get the error: "You have chosen fields from record sources
which
the
wizard can't connect. You may have chosen fields from a table and a
query
on
that table. If so, try choosing fields from only the table or only the
query." The query is based on the table People and not [St Stephen's
Small
Groups], but the 2 Tables are linked by the field IndSmallGroup. Can I
get
there from here?

Thanks again,
Chris


--
Chris F


:

Go back and notice how the number of fields in each "SELECT" in my
union
query was the same. Also, my SELECTs included a primary key field.
--
Duane Hookom
MS Access MVP

message
Duane,
I'm not sure how to set it up. This was my attempt to normalize an
existing
query [GroupLocator], but obviously I have something wrong. Do I
need
an
ID
field? Once the data is normalized, I want to count the number
people
in
each group (the name of each group would be located in the fields
IndSmallgroup, ACCESSADD_List2, ACCESSADD_list3 or ACCESSADD_List4),
but
group the report on City
thank you,
Chris

SELECT [LastName], [FirstName], [City], [GoesByName],
[IndSmallgroup],
[ACCESSADD_List2], [ACCESSADD_List3], [ACCESSADD_List4]
SELECT [IndSmallgroup] as Group
FROM GroupLocator
WHERE [IndSmallgroup] Is not Null
UNION ALL
SELECT ACCESSADD_List2
FROM GroupLocator
WHERE ACCESSADD_List2 Is not Null
UNION ALL
SELECT ACCESSADD_List3
FROM GroupLocator
WHERE ACCESSADD_List3 Is not Null
UNION ALL
SELECT ACCESSADD_List4
FROM GroupLocator
WHERE ACCESSADD_List4 Is not Null
UNION ALL
FROM GroupLocator;

--
Chris F


:

I don't recall what you have right now. Again, you can use a union
query
to
normalize an un-normalized table.

--
Duane Hookom
MS Access MVP

message
Duane,
I'm sorry I haven't answered you before, but I've been trying to
get
the
ACS
person to export the PersonID field into tblPeople without
success.
I
should
get the PersonID in a week. In the mean time, Is it possible to
run
the
union query by record or some other way, until I get a PersonID?
I've
been
trying that without success.
Thanks again,
Chris
--
Chris F


:

If you can't create a junction table, you should be able to
create
a
union
query from your "people" table. For instance if your people
table
had
these
fields:

tblPeople
PersonID
PersonFirstName
PersonLastName
Group1
Group2
Group3

You could create a union query like:

SELECT PersonID, Group1 as Group
FROM tblPeople
WHERE Group1 Is not Null
UNION ALL
SELECT PersonID, Group2
FROM tblPeople
WHERE Group2 Is not Null
UNION ALL
SELECT PersonID, Group3
WHERE Group3 Is not Null;

Duane Hookom
MS Access MVP

message
Duane,
You are correct in how the table People is set up.
Unfortunately, I
am
stuck with how People is set up because ACS (the database
system
where
the
information in maintained and then exported) cannot handle
multiple
records
for 1 individual. Another issue is when personal information
is
changed,
whether it is phone number, email or membership in a group;
there
is
no
unique id number for the record, I have to delete all the data
in
the
table
and paste in the new data.

As far as the Junction table, is that something I can generate
from
the
current People table and can it be automated for when the
People
data
is
updated?

I certainly understand your example, but I still find
normalization
a
tough
concept to understand.

Thanks again for your time and help,
Chris
--
Chris F


:

Apparently you have a record for a person with 4 different
fields
that
might
or might not contain the name of a small group. If so, this
is
not
normalized. I would expect to see a JUNCTION table which
would
contain
records for each person that is a member of a group.