Report design - splitting records into categories

[hect1c]

Hi Access Experts,



I have a union query that generates the following data set:

CategoryDescription	CountofCat
Aboriginal and Torres Strait Islander communities	6
Culturally and Linguistically Diverse	5
Homeless and those at risk of homelessness	1
People who identify as Lesbian, Gay, Bisexual, Transgender and Intersex	14
People with Younger Onset Dementia	2
Regional, rural and remote communities	17
Veterans	1
Metropolitan	25
National	9
Regional	20
Rural Remote	17

Report Wizard spits it out just like that. Which is ok but I cannot figure out how to group it and display specific records like this...

Category 1	CountofCat	Target
National	9	2
Category 2	CountofCat	Target
Aboriginal and Torres Strait Islander communities	6	1
Culturally and Linguistically Diverse	5	1
Homeless and those at risk of homelessness	1	1
People who identify as Lesbian, Gay, Bisexual, Transgender and Intersex	14	1
People with Younger Onset Dementia	2	1
Regional, rural and remote communities	17	1
Category 3	CountofCat	Target
National	9	2
Regional	20	5
Rural Remote	17	1

Please help...

Thank you.

[June7]

Is that a UNION query or an aggregate (GROUP BY) Totals query?

Do you have a table that assigns the category code to each description and has a field for the target value?

[hect1c]

Thank you for your quick reply. That would be both, I used a count and union query. And yes I have a table with category code and category description

My union SQL....
SELECT *
FROM
(SELECT
TblCategory.CategoryDescription,
Count(TblDelivery.DeliveryCat) AS CountofCat,
TblCategory.Target
FROM
TblCategory INNER JOIN (TblActivity INNER JOIN TblDelivery ON TblActivity.ID = TblDelivery.ActivityID) ON TblCategory.CategoryID = TblDelivery.DeliveryCat
WHERE
(((TblActivity.ActivityDate)>=[Start Date] And (TblActivity.ActivityDate)<=[End Date]))
GROUP BY
TblCategory.CategoryDescription,
TblCategory.Target, TblActivity.DTSC
HAVING
(((TblActivity.DTSC)=[Centre]))
)T1
UNION
SELECT *
FROM
(
SELECT
TblCategory.CategoryDescription,
Count(TblSpecialNeeds.SpecialNeedsCat) AS CountofCat,
TblCategory.Target
FROM
TblCategory INNER JOIN (TblActivity INNER JOIN TblSpecialNeeds ON TblActivity.ID = TblSpecialNeeds.ActivityID) ON TblCategory.CategoryID = TblSpecialNeeds.SpecialNeedsCat
WHERE
(((TblActivity.ActivityDate)>=[Start Date] And (TblActivity.ActivityDate)<=[End Date]))
GROUP BY
TblCategory.CategoryDescription,
TblCategory.Target,
TblActivity.DTSC
HAVING
(((TblActivity.DTSC)=[Centre]))
)T2;

[June7]

And the table has target field?

Build another query that joins the query to the table.

Use this query as report RecordSource. Use report Grouping & Sorting features.

[hect1c]

Yes target field as well. How would joining one query to the table work?

Is there a reason the current union query is not suitable as the record source?

In the report design I tried creating specific results via a text box. In control source of this text box I would indicate the following...
=IIf([CategoryDescription]='National',[CountofCat],0)

But I always get a result of zero. The record source of this report has the union query.

[June7]

If you want grouping by Category 1, Category 2, Category 3 then need a field in the RecordSource with that data. You also want the target info in the report.

If that data is already available in the UNION then by all means use it as the report RecordSource. Otherwise options:

1. include the Category and Target fields in each Count aggregate query, those fields will have to be in the GROUP BY clause

2. build another query that joins the UNION to Category/Target table

I do see a Target field in the Count subqueries, but not sure about the Category data (1,2,3). I do see CategoryDescription. Maybe the 1,2,3 is more appropriately referred to as Group.

[hect1c]

Thank your comment trigger a thought process and that I added additional sorting column to my table and it was much easier to group in the report.