Access 97 : How to suppress duplicate information in a complex(?) report?

[Emilio Jimonerz]

Hello. I am developing a project tracking database that records the
following information:
1. Project name
2. Lead person(s)
3. Support person(s)
4. Clients
5. Contacts

The data is organized in the following way:

"tblProject" - list of projects (ie: Project A, Project B...)
"tblPeople" - list of all possible people (ie: Ann, Bill, Chris...)
"tblPeopleRole" - roles of people (ie: Lead, Support, Client, Contact)
"trelProjectPeople" - relation table describing the role of each
personnel assigned to a project

In my report, I want to succinctly show the personnel involved in each
project, ie:

'ID Project Name Lead Support Client Contact
'-----------------------------------------------------
'3 Project A Ann Dan Bill Linda
' Chris
'-----------------------------------------------------
'15 Project B Don Chris Laura
'-----------------------------------------------------

I have used four queries with filters to seperate the roles of the
personnel from "trelProjectPeople", and then another query to rejoin
them with distinct fields names of each role (ie: "Lead", "Support",
etc. are now columns). Then I created a report grouped by the project
id, and used the "Hide duplicates" format of the text boxes to suppress
repeating the name of personnel. Unfortunately, if there are multiple
people assigned to each role, I end up with the following:

'ID Project Name Lead Support Client Contact
'-----------------------------------------------------
'19 Project C David Dan Eric Angela
' Bill
' Marcie Eric
' Bill
'-----------------------------------------------------

Clearly, I only want to show the names of the clients "Eric" and "Bill"
only once:

'ID Project Name Lead Support Client Contact
'-----------------------------------------------------
'19 Project C David Dan Eric Angela
' Marcie Bill
'-----------------------------------------------------

This problem will only be exacerbated when more people are assigned to
the same role. Is there an elegent solution to this? I have previously
used a solution involving four seperate subreports to display columns
for each personnel involved in each of the roles, but I figured it was
too ungainly.

Thanks. Any help would be appreciated.

ej

[Bas Cost Budde]

Emilio Jimonerz wrote:

I have used four queries with filters to seperate the roles of the
personnel from "trelProjectPeople", and then another query to rejoin
them with distinct fields names of each role (ie: "Lead", "Support",
etc. are now columns).
Can't you use a crosstab query instead? Or do you have Null Hell then?
:-) I think using separate queries and rejoins is a sensible approach too.
Clearly, I only want to show the names of the clients "Eric" and "Bill"
only once:

'ID Project Name Lead Support Client Contact
'-----------------------------------------------------
'19 Project C David Dan Eric Angela
' Marcie Bill
'-----------------------------------------------------

If you do query concatenation (those are search terms), you can have one
field per {lead,support,client,contact}; if you set the CanGrow property
for the control, all is done.

You certainly don't want to have multiple *rows* per project as a row
always implicates, at least, some horizontal relationship, which you
clearly *don't* want here.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Emilio Jimonerz]

Bas Cost : Thanks for the direction. I had previously tried in vain
looking for SQL string concatenation but I guess it doesn't exist, and
my last effort by suppressing the duplicates in the report was just
trying to make sense of a messy horizontally-implicated table as you
clearly noted. I ended up using fConcatChild from
http://www.mvps.org/access/modules/mdl0004.htm. However, I just spent
the last day trying to implement it in my database, but I always ended
up with an error 13 : type mismatch. I ended up copying the tables to
a new database and then the function worked. *wtf?!* Oh well. Happy
Holidays and thanks for the help.

ej