Crosstab query with text?

[Noah114]

I have a table that has plan names with multiple id's. For example:

Name: ID:
Blue Cross 12
Blue Cross 25
Blue Cross 15
Health US 14

Can I generate a cross tab or a query that will produce a table that would show:

Blue Cross 12,25,15
Health US 14

 

[lespaul]

here's one way:

FAQ701-3499

if you search the fora for 'concatenation' you will probably find more options.

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Noah114]

Still need help, don't know SQL so most of the refernces I've look at haven't been helpful

 

[lespaul]

what have you tried?

 

[Noah114]

Just simple crosstabs, but that won't work..... It's so frustrating because of how important it is. I can do it in excel, but it's very manual

 

[dhookom]

Here's another function that will concatenate the strings
faq701-4233. You need to copy the function into a new module in your MDB file. Save the module as "basConcatenate".

First create a query like this that has your table and field names:

SELECT [PlanName], Concatenate("SELECT ID FROM tblThatHasPlanNames WHERE [PlanName]=""" & [PlanName] & """") as Plans
FROM tblThatHasPlanNames;

Then make it into a totals query that groups by both columns.

Next time you ask a query question, consider providing actual table and field names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]