Adding columns in cross tab query

[tezread]

I have a cross tab query that results in 47 results. I am looking at the age, gender and ethnicity of patients.
Trouble is 47 lines is too many so i want to group the ethnicities into Asian, White, Other and Black african/carribbean instead of the 12 or so that exist. How can I do that?

 

[the_net_2.0]

what does the query output look like?

 

[tezread]

what does the query output look like?

output looks like one attached. Effectively I want to group the Black African/Carribbean and some other ethnic minorities so I have White, Asian and BME. this is the query itself:

SELECT [Acceptance by ethnicity, gender and age].[ethnic origin], [Acceptance by ethnicity, gender and age].[age group], [Acceptance by ethnicity, gender and age].gender, [Acceptance by ethnicity, gender and age].accepted, [Acceptance by ethnicity, gender and age].declined, [Acceptance by ethnicity, gender and age].[lost to FU], [Acceptance by ethnicity, gender and age].[not referred]
FROM [Acceptance by ethnicity, gender and age];

 

[tezread]

Quelle surprise I am learning after all - I have fixed it myself.

I added this into the original query:

Ethnicgroup: IIf([Ethnic origin] Like "*White*","White","BME")

which means i can group the ethnicity now!

 

[DCrake]

That's fine until you broaden your groupings or change the goal posts. I would be tempted to go to your ethnicity table and add a new field called ReportingGroup and assign each ehnic origin to a reporting group. Then create a new table named EnthnicGroups. With a PK and Description. The primary key will link to the foreign key in your Ethnic Origin table reporting group field.

Then in your cross tab query introduce your EthincGroups table and join PK to FK and group by Ethnic Group Descritpion.

 

[tezread]

That's fine until you broaden your groupings or change the goal posts. I would be tempted to go to your ethnicity table and add a new field called ReportingGroup and assign each ehnic origin to a reporting group. Then create a new table named EnthnicGroups. With a PK and Description. The primary key will link to the foreign key in your Ethnic Origin table reporting group field.

Then in your cross tab query introduce your EthincGroups table and join PK to FK and group by Ethnic Group Descritpion.

Tis good to know for future reference Dcrake thankyou