Sorting data from different centers

[AGOKP4]

Hi,

I created a similar database for use in 3 different centers, the only
difference between the data from these centers is the first letter of the
subject ID (A1001, B1001, and C1001), each corresponding to a different
center.

I have combined the data coming in from these 3 sites using a union query.
Is it possible to summarize by center in a query? The SQL statement below
refers to age group summary, is it possible to have this done in a query (or
any suitable method) by center using the study ID?

Thanks!!

SELECT [Screened Qry].[Age Group], Count([Screened Qry].[Age Group]) AS
[CountOfAge Group]
FROM [Screened Qry]
GROUP BY [Screened Qry].[Age Group];

 

[AGOKP4]

In trying to do this, I think I may need to use an "If ..."statement. The
study IDs have 5 characters ranging from A1001 to A1999 for males, A2001 to
A2999 for females. Same for B and C. the first character refers to the
center and the second for gender.

What kind of If statement can I use for this? I tried

Center: IIf([Study ID]=A***,“Tuftsâ€,IIf([Study ID]=B***,“UABâ€,IIf([Study
ID]=C***,“Mt Sinaiâ€)))


I'd welcome any suggestions

THANKS!!

 

[vanderghast]

Depends of your UNION query:


SELECT field1, field2, ...., "A" AS center FROM A1001
UNION ALL
SELECT field1, field2, ..., "B" FROM B1001
UNION ALL
SELECT field1, field2, ... "C" FROM C1001



then, you can GROUP over the Center field to have your stats 'by center'.


Vanderghast, Access MVP

 

[AGOKP4]

Below is what i have from centers A and B (nothing from C yet) from my union
query. the study IDs have 5 characters ranging from A1001 to A1999 for
males, A2001 to A2999 for females. Same for B and C. the first character
refers to the center and the second for gender.

can I use what you pasted below or use an IIF statement?

SELECT [SCREENING AND ENROLLMENT 2].[Study ID], [SCREENING AND ENROLLMENT
2].Sex, [SCREENING AND ENROLLMENT 2].Age, [SCREENING AND ENROLLMENT 2].Race,
[SCREENING AND ENROLLMENT 2].Ethnicity, [SCREENING AND ENROLLMENT
2].Creatinine, [SCREENING AND ENROLLMENT 2].Tenofovir, [SCREENING AND
ENROLLMENT 2].[Scheduled Screening], [SCREENING AND ENROLLMENT 2].[Had
Screening Visit], [SCREENING AND ENROLLMENT 2].Enrolled, [SCREENING AND
ENROLLMENT 2].[Had study vist], [SCREENING AND ENROLLMENT 2].Screened,
IIf([Age]<40,"40<",IIf([Age]>=40 And [Age]<60,"40-60",IIf([Age]>60,"60>","No
data"))) AS [Age Group], IIf([Creatinine]<1,"1.0<",IIf([Creatinine]>=1 And
[Creatinine]<1.4,"1.0-1.4",IIf([Creatinine]>1.4,"1.4>","No data"))) AS
[Creatinine Group]

FROM [SCREENING AND ENROLLMENT 2];

UNION ALL SELECT [SCREENING AND ENROLLMENT 21].[Study ID], [SCREENING AND
ENROLLMENT 21].Sex, [SCREENING AND ENROLLMENT 21].Age, [SCREENING AND
ENROLLMENT 21].Race, [SCREENING AND ENROLLMENT 21].Ethnicity, [SCREENING AND
ENROLLMENT 21].Creatinine, [SCREENING AND ENROLLMENT 21].Tenofovir,
[SCREENING AND ENROLLMENT 21].[Scheduled Screening], [SCREENING AND
ENROLLMENT 21].[Had Screening Visit], [SCREENING AND ENROLLMENT 21].Enrolled,
[SCREENING AND ENROLLMENT 21].[Had study vist], [SCREENING AND ENROLLMENT
21].Screened, IIf([Age]<40,"40<",IIf([Age]>=40 And
[Age]<60,"40-60",IIf([Age]>60,"60>","No data"))) AS [Age Group],
IIf([Creatinine]<1,"1.0<",IIf([Creatinine]>=1 And
[Creatinine]<1.4,"1.0-1.4",IIf([Creatinine]>1.4,"1.4>","No data"))) AS
[Creatinine Group]

FROM [SCREENING AND ENROLLMENT 21];

Depends of your UNION query:


SELECT field1, field2, ...., "A" AS center FROM A1001
UNION ALL
SELECT field1, field2, ..., "B" FROM B1001
UNION ALL
SELECT field1, field2, ... "C" FROM C1001



then, you can GROUP over the Center field to have your stats 'by center'.


Vanderghast, Access MVP

Hi,

I created a similar database for use in 3 different centers, the only
difference between the data from these centers is the first letter of the
subject ID (A1001, B1001, and C1001), each corresponding to a different
center.

I have combined the data coming in from these 3 sites using a union query.
Is it possible to summarize by center in a query? The SQL statement below
refers to age group summary, is it possible to have this done in a query
(or
any suitable method) by center using the study ID?

Thanks!!

SELECT [Screened Qry].[Age Group], Count([Screened Qry].[Age Group]) AS
[CountOfAge Group]
FROM [Screened Qry]
GROUP BY [Screened Qry].[Age Group];

 

[vanderghast]

It is always preferable to have data in an atomic way, not in a
'combination' / 'encoded' way.


SELECT Left([SCREENING AND ENROLLMENT 2].[Study ID], 1) AS center,
[SCREENING AND ENROLLMENT
2].Sex, [SCREENING AND ENROLLMENT 2].Age, [SCREENING AND ENROLLMENT 2].Race,
[SCREENING AND ENROLLMENT 2].Ethnicity, [SCREENING AND ENROLLMENT
2].Creatinine, [SCREENING AND ENROLLMENT 2].Tenofovir, [SCREENING AND
ENROLLMENT 2].[Scheduled Screening], [SCREENING AND ENROLLMENT 2].[Had
Screening Visit], [SCREENING AND ENROLLMENT 2].Enrolled, [SCREENING AND
ENROLLMENT 2].[Had study vist], [SCREENING AND ENROLLMENT 2].Screened,
IIf([Age]<40,"40<",IIf([Age]>=40 And [Age]<60,"40-60",IIf([Age]>60,"60>","No
data"))) AS [Age Group], IIf([Creatinine]<1,"1.0<",IIf([Creatinine]>=1 And
[Creatinine]<1.4,"1.0-1.4",IIf([Creatinine]>1.4,"1.4>","No data"))) AS
[Creatinine Group]

FROM [SCREENING AND ENROLLMENT 2];

UNION ALL SELECT Left([SCREENING AND ENROLLMENT 21].[Study ID], 1), ...


as example (you already have a sex field, so checking if it is between A1001
and A1999 or A2001 and A2999 is not relevant, and what would you do if
there was a contradiction with the value in the field sex, anyhow?


So, now having the field Center, you can, in a new query using this union
query, easily GROUP over that field.


Sure, you can also GROUP on a computed expression,


... GROUP BY Left([Study ID], 1)


but that would be probably slower than grouping on an existing field.



Vanderghast, Access MVP

Below is what i have from centers A and B (nothing from C yet) from my
union
query. the study IDs have 5 characters ranging from A1001 to A1999 for
males, A2001 to A2999 for females. Same for B and C. the first character
refers to the center and the second for gender.

can I use what you pasted below or use an IIF statement?

SELECT [SCREENING AND ENROLLMENT 2].[Study ID], [SCREENING AND ENROLLMENT
2].Sex, [SCREENING AND ENROLLMENT 2].Age, [SCREENING AND ENROLLMENT
2].Race,
[SCREENING AND ENROLLMENT 2].Ethnicity, [SCREENING AND ENROLLMENT
2].Creatinine, [SCREENING AND ENROLLMENT 2].Tenofovir, [SCREENING AND
ENROLLMENT 2].[Scheduled Screening], [SCREENING AND ENROLLMENT 2].[Had
Screening Visit], [SCREENING AND ENROLLMENT 2].Enrolled, [SCREENING AND
ENROLLMENT 2].[Had study vist], [SCREENING AND ENROLLMENT 2].Screened,
IIf([Age]<40,"40<",IIf([Age]>=40 And
[Age]<60,"40-60",IIf([Age]>60,"60>","No
data"))) AS [Age Group], IIf([Creatinine]<1,"1.0<",IIf([Creatinine]>=1 And
[Creatinine]<1.4,"1.0-1.4",IIf([Creatinine]>1.4,"1.4>","No data"))) AS
[Creatinine Group]

FROM [SCREENING AND ENROLLMENT 2];

UNION ALL SELECT [SCREENING AND ENROLLMENT 21].[Study ID], [SCREENING AND
ENROLLMENT 21].Sex, [SCREENING AND ENROLLMENT 21].Age, [SCREENING AND
ENROLLMENT 21].Race, [SCREENING AND ENROLLMENT 21].Ethnicity, [SCREENING
AND
ENROLLMENT 21].Creatinine, [SCREENING AND ENROLLMENT 21].Tenofovir,
[SCREENING AND ENROLLMENT 21].[Scheduled Screening], [SCREENING AND
ENROLLMENT 21].[Had Screening Visit], [SCREENING AND ENROLLMENT
21].Enrolled,
[SCREENING AND ENROLLMENT 21].[Had study vist], [SCREENING AND ENROLLMENT
21].Screened, IIf([Age]<40,"40<",IIf([Age]>=40 And
[Age]<60,"40-60",IIf([Age]>60,"60>","No data"))) AS [Age Group],
IIf([Creatinine]<1,"1.0<",IIf([Creatinine]>=1 And
[Creatinine]<1.4,"1.0-1.4",IIf([Creatinine]>1.4,"1.4>","No data"))) AS
[Creatinine Group]

FROM [SCREENING AND ENROLLMENT 21];

Depends of your UNION query:


SELECT field1, field2, ...., "A" AS center FROM A1001
UNION ALL
SELECT field1, field2, ..., "B" FROM B1001
UNION ALL
SELECT field1, field2, ... "C" FROM C1001



then, you can GROUP over the Center field to have your stats 'by
center'.


Vanderghast, Access MVP

Hi,

I created a similar database for use in 3 different centers, the only
difference between the data from these centers is the first letter of
the
subject ID (A1001, B1001, and C1001), each corresponding to a different
center.

I have combined the data coming in from these 3 sites using a union
query.
Is it possible to summarize by center in a query? The SQL statement
below
refers to age group summary, is it possible to have this done in a
query
(or
any suitable method) by center using the study ID?

Thanks!!

SELECT [Screened Qry].[Age Group], Count([Screened Qry].[Age Group]) AS
[CountOfAge Group]
FROM [Screened Qry]
GROUP BY [Screened Qry].[Age Group];

 

[AGOKP4]

Hi vanderghast

Thanks so much your help. I was able to use the 1st part of your answer to
do the sorting. I created a new query (see SQL below) How can sort this by
center?


SELECT "Age" as Source, [Tufts 2 + UAB 21 + Mt Sinai 22].[Age Group],
Count([Tufts 2 + UAB 21 + Mt Sinai 22].[Age Group]) AS [CountOfAge Group]
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].[Age Group];

UNION ALL
SELECT "Sex" as Source, [Tufts 2 + UAB 21 + Mt Sinai 22].Sex, Count([Tufts 2
+ UAB 21 + Mt Sinai 22].Sex) AS CountOfSex
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].Sex;

UNION ALL
SELECT "Race" as Source, [Tufts 2 + UAB 21 + Mt Sinai 22].Race,
Count([Tufts 2 + UAB 21 + Mt Sinai 22].Race) AS CountOfRace
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].Race;

UNION ALL
SELECT "Ethnicity" as Source, [Tufts 2 + UAB 21 + Mt Sinai 22].Ethnicity,
Count([Tufts 2 + UAB 21 + Mt Sinai 22].Ethnicity) AS CountOfEthnicity
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].Ethnicity;

UNION ALL
SELECT "Tenofovir" as Source, [Tufts 2 + UAB 21 + Mt Sinai 22].Tenofovir,
Count([Tufts 2 + UAB 21 + Mt Sinai 22].Tenofovir) AS CountOfTenofovir
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].Tenofovir;

UNION ALL
SELECT "Creatinine Group" as Source, [Tufts 2 + UAB 21 + Mt Sinai
22].[Creatinine Group], Count([Tufts 2 + UAB 21 + Mt Sinai 22].[Creatinine
Group]) AS [CountOfCreatinine Group]
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].[Creatinine Group];

 

[John W. Vinson]

Hi,

I created a similar database for use in 3 different centers, the only
difference between the data from these centers is the first letter of the
subject ID (A1001, B1001, and C1001), each corresponding to a different
center.

I have combined the data coming in from these 3 sites using a union query.
Is it possible to summarize by center in a query? The SQL statement below
refers to age group summary, is it possible to have this done in a query (or
any suitable method) by center using the study ID?

Thanks!!

SELECT [Screened Qry].[Age Group], Count([Screened Qry].[Age Group]) AS
[CountOfAge Group]
FROM [Screened Qry]
GROUP BY [Screened Qry].[Age Group];

Sure. You can also group by Left([SubjectID], 1) to get all the A's together,
then all the B's and so on.

 

[vanderghast]

When using an UNION ALL query, you can add ONE, and only one, ORDER BY, at
the end, to sort the data. You have to use the field names (or alias of
computed expression) you used in the first SELECT query of the UNION query.
Since your first select query does not show the 'center', I assume you HAVE
TO add it, and in the SELECT and in the GROUP BY:

SELECT "Age" as Source,
[Tufts 2 + UAB 21 + Mt Sinai 22].[Age Group],
Count([Tufts 2 + UAB 21 + Mt Sinai 22].[Age Group]) AS [CountOfAge
Group],
Center ' <----- here
FROM [Tufts 2 + UAB 21 + Mt Sinai 22]
GROUP BY [Tufts 2 + UAB 21 + Mt Sinai 22].[Age Group],
Center ' <--- here

UNION ALL
...


(note that I also removed the ; you were having at the end of the GROUP BY
clause).


You will have to make the same modifications for each SELECT query of your
UNION ALL.


Then, at the end of the whole monster, add:



ORDER BY center, source


as example, to sort by center (and by source, within each center).




Vanderghast, Access MVP

 

[AGOKP4]

Hi,

Thanks for your reply. I included the SQL statement (see below) and got the
error message - The ORDER BY expression <expression> includes fields that are
not selected by the query. Only those fields requested in the first query can
be included in an ORDER BY expression. (Error 3351) -

How can I address this


THANKS so much

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

 

[vanderghast]

It seems there is an extra character after Center, at each SELECT queries
(two places per query, once just before FROM and one at the end of the GROUP
BY.

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...


try to remove it:

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...



Vanderghast, Access MVP

 

[AGOKP4]

Worked PERFECTLY....THANKS!! I working on the report now and trying to
arrange the centers as columns.

Have a great weekend!! i would let you know if i have further questions