Combining Queries

A

AGOKP4

Hi,

I have a query I'm trying to "split" so it comes out by sex. the original
query is:

TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group]
PIVOT [Enrolled Qry].Tenofovir;


The individual queries (by sex) are:

TRANSFORM Count([Enrolled F Qry].Tenofovir) AS CountOfTenofovir
SELECT [Enrolled F Qry].[Creatinine Group], Count([Enrolled F
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled F Qry]
GROUP BY [Enrolled F Qry].[Creatinine Group]
PIVOT [Enrolled F Qry].Tenofovir;

TRANSFORM Count([Enrolled M Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled M Qry].[Creatinine Group], Count([Enrolled M
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled M Qry]
GROUP BY [Enrolled M Qry].[Creatinine Group]
PIVOT [Enrolled M Qry].Tenofovir;


Can I write 1 query to report it by sex OR combine the individual queries M
and F above?


Thanks!!!

2 individual queries (by sex) i'm trying to combine to have 1 query.

initially i had
 
K

KARL DEWEY

Try this using the name of your field designating gender --
TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group], [Sex]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]), [Sex]
PIVOT [Enrolled Qry].Tenofovir;
 
A

AGOKP4

HI,

Thanks for your reply. I tried it but got the error message:

Cannot have aggregate function in GROUP BY clause Count([Enrolled
Qry].[Creatinine Group]))



KARL DEWEY said:
Try this using the name of your field designating gender --
TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group], [Sex]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]), [Sex]
PIVOT [Enrolled Qry].Tenofovir;

--
Build a little, test a little.


AGOKP4 said:
Hi,

I have a query I'm trying to "split" so it comes out by sex. the original
query is:

TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group]
PIVOT [Enrolled Qry].Tenofovir;


The individual queries (by sex) are:

TRANSFORM Count([Enrolled F Qry].Tenofovir) AS CountOfTenofovir
SELECT [Enrolled F Qry].[Creatinine Group], Count([Enrolled F
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled F Qry]
GROUP BY [Enrolled F Qry].[Creatinine Group]
PIVOT [Enrolled F Qry].Tenofovir;

TRANSFORM Count([Enrolled M Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled M Qry].[Creatinine Group], Count([Enrolled M
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled M Qry]
GROUP BY [Enrolled M Qry].[Creatinine Group]
PIVOT [Enrolled M Qry].Tenofovir;


Can I write 1 query to report it by sex OR combine the individual queries M
and F above?


Thanks!!!

2 individual queries (by sex) i'm trying to combine to have 1 query.

initially i had
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try changing this way --
GROUP BY [Enrolled Qry].[Creatinine Group], [Sex]

--
Build a little, test a little.


AGOKP4 said:
HI,

Thanks for your reply. I tried it but got the error message:

Cannot have aggregate function in GROUP BY clause Count([Enrolled
Qry].[Creatinine Group]))



KARL DEWEY said:
Try this using the name of your field designating gender --
TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group], [Sex]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]), [Sex]
PIVOT [Enrolled Qry].Tenofovir;

--
Build a little, test a little.


AGOKP4 said:
Hi,

I have a query I'm trying to "split" so it comes out by sex. the original
query is:

TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group]
PIVOT [Enrolled Qry].Tenofovir;


The individual queries (by sex) are:

TRANSFORM Count([Enrolled F Qry].Tenofovir) AS CountOfTenofovir
SELECT [Enrolled F Qry].[Creatinine Group], Count([Enrolled F
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled F Qry]
GROUP BY [Enrolled F Qry].[Creatinine Group]
PIVOT [Enrolled F Qry].Tenofovir;

TRANSFORM Count([Enrolled M Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled M Qry].[Creatinine Group], Count([Enrolled M
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled M Qry]
GROUP BY [Enrolled M Qry].[Creatinine Group]
PIVOT [Enrolled M Qry].Tenofovir;


Can I write 1 query to report it by sex OR combine the individual queries M
and F above?


Thanks!!!

2 individual queries (by sex) i'm trying to combine to have 1 query.

initially i had
Click to expand...
Click to expand...
Click to expand...
 
A

AGOKP4

Thanks...it worked great

KARL DEWEY said:
Try changing this way --
GROUP BY [Enrolled Qry].[Creatinine Group], [Sex]

--
Build a little, test a little.


AGOKP4 said:
HI,

Thanks for your reply. I tried it but got the error message:

Cannot have aggregate function in GROUP BY clause Count([Enrolled
Qry].[Creatinine Group]))



KARL DEWEY said:
Try this using the name of your field designating gender --
TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group], [Sex]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]), [Sex]
PIVOT [Enrolled Qry].Tenofovir;

--
Build a little, test a little.


:

Hi,

I have a query I'm trying to "split" so it comes out by sex. the original
query is:

TRANSFORM Count([Enrolled Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled Qry].[Creatinine Group], Count([Enrolled Qry].[Creatinine
Group]) AS [CountOfCreatinine Group]
FROM [Enrolled Qry]
GROUP BY [Enrolled Qry].[Creatinine Group]
PIVOT [Enrolled Qry].Tenofovir;


The individual queries (by sex) are:

TRANSFORM Count([Enrolled F Qry].Tenofovir) AS CountOfTenofovir
SELECT [Enrolled F Qry].[Creatinine Group], Count([Enrolled F
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled F Qry]
GROUP BY [Enrolled F Qry].[Creatinine Group]
PIVOT [Enrolled F Qry].Tenofovir;

TRANSFORM Count([Enrolled M Qry].[Study ID]) AS [CountOfStudy ID]
SELECT [Enrolled M Qry].[Creatinine Group], Count([Enrolled M
Qry].[Creatinine Group]) AS [CountOfCreatinine Group]
FROM [Enrolled M Qry]
GROUP BY [Enrolled M Qry].[Creatinine Group]
PIVOT [Enrolled M Qry].Tenofovir;


Can I write 1 query to report it by sex OR combine the individual queries M
and F above?


Thanks!!!

2 individual queries (by sex) i'm trying to combine to have 1 query.

initially i had
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine Crosstab Queries? 2
Union Queries 1
Combining Queries (2) 1
Joining Crosstab queries 3
Combine Queries (2) 6
Report by group in columns 8
Sorting data from different centers 10
query too slow 1

Top