Help to create a consolidation report

[SueD]

I am running Access 2000 and have a table where each record (amongst other
things) contains 8 fields for people's names. There could be a different set
of 8 people on each record.

I need a report that tells me how many times each person's name appears in
the database, but their names could appear in different one of the 8 fields
each time it is present.

I have made a parameter query to search on one name at time (in all 8
fields), but cannot work out how to make one report showing all names and how
many appearances it makes.

 

[Allen Browne]

Sue, the problem here is the way the table is designed.

Whatever your records are about, it seems that each one can be associated
with many people. Presumably these people can also be associated with
different records. This implies you have a many-to-many relation between
whatevers and people, so you will need 3 tables:

Whatever table (the one you already have), with a WhateverID primary key.
Person table, with a PersonID primary key.
WhateverPerson table, with fields:
- WhateverID (matches a record in your existing table)
- PersonID (matches a record in your Person table.)

So, if your record 1 is associated with persons 2, 5, 6, and 7, the third
table will have records like this:
1 2
1 5
1 6
1 7

You interface this with a main form bound to your Whatever table, and a
subform bound to the third table. The subform is in Continuous Form view, so
you can add as many rows as you need for the people associated with your
items. You can use a combo in the subform for selecting the people (so you
don't have to remember their numbers.)

Once you have that set up, you remove the 8 'person' fields from your
Whatever table.

This makes it really easy to query and report in the way you want to.

What you have just read is absolutely crucial to database design: using many
records in a related table, instead of many repeating fields on one table.
This is called 'normalization', and is the heart and soul of any database.
For another example, see:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

To give you some idea of important it is to get this right, here's some
starting links dealing with the subject of normzliation:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Jake]

Sue,

What Alen described is the "best" way to go about this but here is another
"down and dirty" option. You can use a "union all" query to make a list of
all of the names.

You will need to use the "SQL" fiew of the query to enter it. It would look
something like this:

SELECT Name1 FROM MyTable
UNION ALL
SELECT Name2 FROM MyTable
UNION ALL
SELECT Name3 FROM MyTable
UNION ALL
SELECT Name4 FROM MyTable
UNION ALL
SELECT Name5 FROM MyTable
UNION ALL
SELECT Name6 FROM MyTable
UNION ALL
SELECT Name7 FROM MyTable
UNION ALL
SELECT Name8 FROM MyTable;

IMPORTANT: you must use "union all". "union" by itself will remove
duplicates making it imposible to count the names.

This query will contain a list of all names in all 8 fields. You can then
make an agragate query to count and/or sort the names using this query as its
source.

Jacob

 

[SueD]

Thanks Jake - This worked like a dream. I do have the people in a separate
table using codes as suggested in the previous post, but I'm not fully
"normalised"!