Report by group in columns

  • Thread starter Thread starter AGOKP4
  • Start date Start date
A

AGOKP4

Hi,

I managed to create a query of the same kind of data for 2 groups (see SQL
query below) with your help and would now like to make a report with the
groups in columns and the results in rows eg
A B
age xx xx
sex xx xx
ethnicity... xx xx

SELECT "Age" as Source,
[ALL BY CENTER QRY].[Age Group],
Count([ALL BY CENTER QRY].[Age Group]) AS [CountOfAge Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Age Group],
Center
UNION ALL
SELECT "Sex" as Source,
[ALL BY CENTER QRY].Sex,
Count([ALL BY CENTER QRY].Sex) AS CountOfSex,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Sex,
Center
UNION ALL
SELECT "Race" as Source,
[ALL BY CENTER QRY].Race,
Count([ALL BY CENTER QRY].Race) AS CountOfRace,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Race,
Center
UNION ALL
SELECT "Ethnicity" as Source,
[ALL BY CENTER QRY].Ethnicity,
Count([ALL BY CENTER QRY].Ethnicity) AS CountOfEthnicity,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Ethnicity,
Center
UNION ALL
SELECT "Tenofovir" as Source,
[ALL BY CENTER QRY].Tenofovir,
Count([ALL BY CENTER QRY].Tenofovir) AS CountOfTenofovir,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Tenofovir,
Center
UNION ALL SELECT "Creatinine Group" as Source,
[ALL BY CENTER QRY].[Creatinine Group],
Count([ALL BY CENTER QRY].[Creatinine Group]) AS [CountOfCreatinine Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Creatinine Group],
Center
ORDER BY Center, source;
 
Try changinging your union as below then use a crosstab --
qryAllCenter --
SELECT "Age" as Source, [ALL BY CENTER QRY].[Age Group] AS Factor, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Sex" as Source, [ALL BY CENTER QRY].Sex, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Race" as Source, [ALL BY CENTER QRY].Race, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Ethnicity" as Source, [ALL BY CENTER QRY].Ethnicity, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Tenofovir" as Source, [ALL BY CENTER QRY].Tenofovir, Center
FROM [ALL BY CENTER QRY]
UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

TRANSFORM Count(qryAllCenter.Factor) AS CountOfValue
SELECT qryAllCenter.Source, Factor
FROM qryAllCenter
GROUP BY qryAllCenter.Source, qryAllCenter.Factor
PIVOT qryAllCenter.Center;
 
Hello Karl,

Thanks for your reply, I pasted the SQL statement you gave but got the error
message

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.



KARL DEWEY said:
Try changinging your union as below then use a crosstab --
qryAllCenter --
SELECT "Age" as Source, [ALL BY CENTER QRY].[Age Group] AS Factor, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Sex" as Source, [ALL BY CENTER QRY].Sex, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Race" as Source, [ALL BY CENTER QRY].Race, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Ethnicity" as Source, [ALL BY CENTER QRY].Ethnicity, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Tenofovir" as Source, [ALL BY CENTER QRY].Tenofovir, Center
FROM [ALL BY CENTER QRY]
UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

TRANSFORM Count(qryAllCenter.Factor) AS CountOfValue
SELECT qryAllCenter.Source, Factor
FROM qryAllCenter
GROUP BY qryAllCenter.Source, qryAllCenter.Factor
PIVOT qryAllCenter.Center;

--
Build a little, test a little.


AGOKP4 said:
Hi,

I managed to create a query of the same kind of data for 2 groups (see SQL
query below) with your help and would now like to make a report with the
groups in columns and the results in rows eg
A B
age xx xx
sex xx xx
ethnicity... xx xx

SELECT "Age" as Source,
[ALL BY CENTER QRY].[Age Group],
Count([ALL BY CENTER QRY].[Age Group]) AS [CountOfAge Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Age Group],
Center
UNION ALL
SELECT "Sex" as Source,
[ALL BY CENTER QRY].Sex,
Count([ALL BY CENTER QRY].Sex) AS CountOfSex,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Sex,
Center
UNION ALL
SELECT "Race" as Source,
[ALL BY CENTER QRY].Race,
Count([ALL BY CENTER QRY].Race) AS CountOfRace,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Race,
Center
UNION ALL
SELECT "Ethnicity" as Source,
[ALL BY CENTER QRY].Ethnicity,
Count([ALL BY CENTER QRY].Ethnicity) AS CountOfEthnicity,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Ethnicity,
Center
UNION ALL
SELECT "Tenofovir" as Source,
[ALL BY CENTER QRY].Tenofovir,
Count([ALL BY CENTER QRY].Tenofovir) AS CountOfTenofovir,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Tenofovir,
Center
UNION ALL SELECT "Creatinine Group" as Source,
[ALL BY CENTER QRY].[Creatinine Group],
Count([ALL BY CENTER QRY].[Creatinine Group]) AS [CountOfCreatinine Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Creatinine Group],
Center
ORDER BY Center, source;
Click to expand...
Click to expand...
 
Did you create two queries as suggested by Karl?

--
Duane Hookom
Microsoft Access MVP


AGOKP4 said:
Hello Karl,

Thanks for your reply, I pasted the SQL statement you gave but got the error
message

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.



KARL DEWEY said:
Try changinging your union as below then use a crosstab --
qryAllCenter --
SELECT "Age" as Source, [ALL BY CENTER QRY].[Age Group] AS Factor, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Sex" as Source, [ALL BY CENTER QRY].Sex, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Race" as Source, [ALL BY CENTER QRY].Race, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Ethnicity" as Source, [ALL BY CENTER QRY].Ethnicity, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Tenofovir" as Source, [ALL BY CENTER QRY].Tenofovir, Center
FROM [ALL BY CENTER QRY]
UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

TRANSFORM Count(qryAllCenter.Factor) AS CountOfValue
SELECT qryAllCenter.Source, Factor
FROM qryAllCenter
GROUP BY qryAllCenter.Source, qryAllCenter.Factor
PIVOT qryAllCenter.Center;

--
Build a little, test a little.


AGOKP4 said:
Hi,

I managed to create a query of the same kind of data for 2 groups (see SQL
query below) with your help and would now like to make a report with the
groups in columns and the results in rows eg
A B
age xx xx
sex xx xx
ethnicity... xx xx

SELECT "Age" as Source,
[ALL BY CENTER QRY].[Age Group],
Count([ALL BY CENTER QRY].[Age Group]) AS [CountOfAge Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Age Group],
Center
UNION ALL
SELECT "Sex" as Source,
[ALL BY CENTER QRY].Sex,
Count([ALL BY CENTER QRY].Sex) AS CountOfSex,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Sex,
Center
UNION ALL
SELECT "Race" as Source,
[ALL BY CENTER QRY].Race,
Count([ALL BY CENTER QRY].Race) AS CountOfRace,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Race,
Center
UNION ALL
SELECT "Ethnicity" as Source,
[ALL BY CENTER QRY].Ethnicity,
Count([ALL BY CENTER QRY].Ethnicity) AS CountOfEthnicity,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Ethnicity,
Center
UNION ALL
SELECT "Tenofovir" as Source,
[ALL BY CENTER QRY].Tenofovir,
Count([ALL BY CENTER QRY].Tenofovir) AS CountOfTenofovir,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Tenofovir,
Center
UNION ALL SELECT "Creatinine Group" as Source,
[ALL BY CENTER QRY].[Creatinine Group],
Count([ALL BY CENTER QRY].[Creatinine Group]) AS [CountOfCreatinine Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Creatinine Group],
Center
ORDER BY Center, source;
Click to expand...
Click to expand...
Click to expand...
 
no i didn't....how do i do that?

Duane Hookom said:
Did you create two queries as suggested by Karl?

--
Duane Hookom
Microsoft Access MVP


AGOKP4 said:
Hello Karl,

Thanks for your reply, I pasted the SQL statement you gave but got the error
message

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.



KARL DEWEY said:
Try changinging your union as below then use a crosstab --
qryAllCenter --
SELECT "Age" as Source, [ALL BY CENTER QRY].[Age Group] AS Factor, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Sex" as Source, [ALL BY CENTER QRY].Sex, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Race" as Source, [ALL BY CENTER QRY].Race, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Ethnicity" as Source, [ALL BY CENTER QRY].Ethnicity, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Tenofovir" as Source, [ALL BY CENTER QRY].Tenofovir, Center
FROM [ALL BY CENTER QRY]
UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

TRANSFORM Count(qryAllCenter.Factor) AS CountOfValue
SELECT qryAllCenter.Source, Factor
FROM qryAllCenter
GROUP BY qryAllCenter.Source, qryAllCenter.Factor
PIVOT qryAllCenter.Center;

--
Build a little, test a little.


:

Hi,

I managed to create a query of the same kind of data for 2 groups (see SQL
query below) with your help and would now like to make a report with the
groups in columns and the results in rows eg
A B
age xx xx
sex xx xx
ethnicity... xx xx

SELECT "Age" as Source,
[ALL BY CENTER QRY].[Age Group],
Count([ALL BY CENTER QRY].[Age Group]) AS [CountOfAge Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Age Group],
Center
UNION ALL
SELECT "Sex" as Source,
[ALL BY CENTER QRY].Sex,
Count([ALL BY CENTER QRY].Sex) AS CountOfSex,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Sex,
Center
UNION ALL
SELECT "Race" as Source,
[ALL BY CENTER QRY].Race,
Count([ALL BY CENTER QRY].Race) AS CountOfRace,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Race,
Center
UNION ALL
SELECT "Ethnicity" as Source,
[ALL BY CENTER QRY].Ethnicity,
Count([ALL BY CENTER QRY].Ethnicity) AS CountOfEthnicity,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Ethnicity,
Center
UNION ALL
SELECT "Tenofovir" as Source,
[ALL BY CENTER QRY].Tenofovir,
Count([ALL BY CENTER QRY].Tenofovir) AS CountOfTenofovir,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Tenofovir,
Center
UNION ALL SELECT "Creatinine Group" as Source,
[ALL BY CENTER QRY].[Creatinine Group],
Count([ALL BY CENTER QRY].[Creatinine Group]) AS [CountOfCreatinine Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Creatinine Group],
Center
ORDER BY Center, source;
Click to expand...
Click to expand...
Click to expand...
 
Name the first one qryAllCenter but the second starts with TRANSFORM.
--
Build a little, test a little.


AGOKP4 said:
no i didn't....how do i do that?

Duane Hookom said:
Did you create two queries as suggested by Karl?

--
Duane Hookom
Microsoft Access MVP


AGOKP4 said:
Hello Karl,

Thanks for your reply, I pasted the SQL statement you gave but got the error
message

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.



:

Try changinging your union as below then use a crosstab --
qryAllCenter --
SELECT "Age" as Source, [ALL BY CENTER QRY].[Age Group] AS Factor, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Sex" as Source, [ALL BY CENTER QRY].Sex, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Race" as Source, [ALL BY CENTER QRY].Race, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Ethnicity" as Source, [ALL BY CENTER QRY].Ethnicity, Center
FROM [ALL BY CENTER QRY]
UNION ALL
SELECT "Tenofovir" as Source, [ALL BY CENTER QRY].Tenofovir, Center
FROM [ALL BY CENTER QRY]
UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

TRANSFORM Count(qryAllCenter.Factor) AS CountOfValue
SELECT qryAllCenter.Source, Factor
FROM qryAllCenter
GROUP BY qryAllCenter.Source, qryAllCenter.Factor
PIVOT qryAllCenter.Center;

--
Build a little, test a little.


:

Hi,

I managed to create a query of the same kind of data for 2 groups (see SQL
query below) with your help and would now like to make a report with the
groups in columns and the results in rows eg
A B
age xx xx
sex xx xx
ethnicity... xx xx

SELECT "Age" as Source,
[ALL BY CENTER QRY].[Age Group],
Count([ALL BY CENTER QRY].[Age Group]) AS [CountOfAge Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Age Group],
Center
UNION ALL
SELECT "Sex" as Source,
[ALL BY CENTER QRY].Sex,
Count([ALL BY CENTER QRY].Sex) AS CountOfSex,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Sex,
Center
UNION ALL
SELECT "Race" as Source,
[ALL BY CENTER QRY].Race,
Count([ALL BY CENTER QRY].Race) AS CountOfRace,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Race,
Center
UNION ALL
SELECT "Ethnicity" as Source,
[ALL BY CENTER QRY].Ethnicity,
Count([ALL BY CENTER QRY].Ethnicity) AS CountOfEthnicity,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Ethnicity,
Center
UNION ALL
SELECT "Tenofovir" as Source,
[ALL BY CENTER QRY].Tenofovir,
Count([ALL BY CENTER QRY].Tenofovir) AS CountOfTenofovir,
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].Tenofovir,
Center
UNION ALL SELECT "Creatinine Group" as Source,
[ALL BY CENTER QRY].[Creatinine Group],
Count([ALL BY CENTER QRY].[Creatinine Group]) AS [CountOfCreatinine Group],
Center
FROM [ALL BY CENTER QRY]
GROUP BY [ALL BY CENTER QRY].[Creatinine Group],
Center
ORDER BY Center, source;
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I still don't get it. Do I copy paste the SQL you gave into a new
query page? If yes, what do I name "qryAllCenter"? and whats the second
refer to?

I apologize for all these questions, I'm relatively new at this

Thanks!!!
 
Copy what I posted starting with -
SELECT ..
through
....UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

and name it qryAllCenter to be used as source for the next query
(the second).

Copy the post starting with -
TRANSFORM ...
through
.... PIVOT qryAllCenter.Center;

and name it anything you want to.

Run the first query alone to test it. Then run the second.

--
Build a little, test a little.


AGOKP4 said:
Sorry, I still don't get it. Do I copy paste the SQL you gave into a new
query page? If yes, what do I name "qryAllCenter"? and whats the second
refer to?

I apologize for all these questions, I'm relatively new at this

Thanks!!!
Click to expand...
 
it worked perfectly, THANKS...

KARL DEWEY said:
Copy what I posted starting with -
SELECT ..
through
...UNION ALL SELECT "Creatinine Group" as Source, [ALL BY CENTER
QRY].[Creatinine Group], Center
FROM [ALL BY CENTER QRY];

and name it qryAllCenter to be used as source for the next query
(the second).

Copy the post starting with -
TRANSFORM ...
through
... PIVOT qryAllCenter.Center;

and name it anything you want to.

Run the first query alone to test it. Then run the second.
Click to expand...
 
Back
Top