Crosstab w/ aggregate criteria

[Leann]

Hello, I am trying to create a crosstab query that will
display rep ids for people who are assigned multiple reps
in a non-normalized table. For example:

Jane, Doe, 111-11-1111, Rep001
Jane, Doe, 111-11-1111, Rep002
John, Doe, 222-22-2222, Rep004

I would want as a result:
Jane, Doe, Rep001, Rep002

I created a simple query that shows clients with more than
one rep assigned. But, of course, it does not tell me who
the reps are. I created a crosstab query that shows
EVERYONE's rep's. But, with over 2500 records, this is
too much data to look through. Is there a way of creating
a crosstab query based upon an aggregate (i.e. >1 rep)? I
have tried to use criteria for the crosstab with no luck.
Any suggestions are welcomed...

Thanks,
L

 

[Duane Hookom]

You might be able to use the generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Thank you Duane. I looked over your queries and unfortunately, I need to be able to set criteria. For example, using your examples - list the family members for all families that have more than 5 members only. Is there a way of doing this with your queries? If not, I may be able to use your tip elsewhere. Thank you very much for responding...

L

 

[Duane Hookom]

You can easily create a query of families with more than 5 members. Use this
as the main source for your concatenate query.

--
Duane Hookom
MS Access MVP
--

Thank you Duane. I looked over your queries and unfortunately, I need to

be able to set criteria. For example, using your examples - list the family
members for all families that have more than 5 members only. Is there a way
of doing this with your queries? If not, I may be able to use your tip
elsewhere. Thank you very much for responding...

 

[Guest]

Hi Duane,

I didn't understand your response. Could you please explain it in more detail?

Thanks!
L