Re: SQL for Counting 1 total of 4 fields - normalization issue onl

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

"Chris Fillar" <ChrisFillar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:227C94AA-6983-4570-8570-D4566CA29698@xxxxxxxxxxxxxxxx
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


"Duane Hookom" wrote:

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

"Chris Fillar" <ChrisFillar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F9AC2F97-5FAE-4822-83DE-A6008F3299FF@xxxxxxxxxxxxxxxx
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


"Duane Hookom" wrote:

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

"Chris Fillar" <ChrisFillar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:DA7151D2-74C6-4378-A4C0-02C57119D581@xxxxxxxxxxxxxxxx
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


"Duane Hookom" wrote:

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

"Chris Fillar" <ChrisFillar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:8CD9DCA3-DFC2-4BBD-B9BA-998F9A126DAB@xxxxxxxxxxxxxxxx
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


"tina" wrote:

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

hth


"Chris Fillar" <ChrisFillar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:1D7FB0F1-35BF-4400-9EC4-4DFD75C94C09@xxxxxxxxxxxxxxxx
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 pop1@xxxxxxxxxxx Ull Bense/Steel
Ull Sue 5 Little Rd Sewickley PA 15143 Member pop1@xxxxxxxxxxx
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


"Duane Hookom" wrote:

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

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

--
Duane Hookom
MS Access MVP
--

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

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

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


.

Relevant Pages
Loading