Combine Queries (2)

[AGOKP4]

Hi,

Is it possible to combine these queries below to get a single one?

Thanks!!!

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

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

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

 

[Jerry Whittle]

Start with a UNION ALL query like below. Then do the Count and Group By. Last
turn it into a crosstab.

SELECT [Screened Qry Mt Sinai].[Creatinine Group],
[Screened Qry Mt Sinai].[Sex]
FROM [Screened Qry Mt Sinai]
UNION ALL
SELECT [Screened Qry Tufts].[Creatinine Group],
[Screened Qry Tufts].Sex,
FROM [Screened Qry Tufts]
UNION ALL
SELECT [Screened Qry UAB].[Creatinine Group],
[Screened Qry UAB].[Sex]
FROM [Screened Qry UAB];

You might even be able to cut things shorter. Are the three queries above
based on selecting from the same table? If so, you may be adding an extra
step.

If they are querying 3 different tables, then your database is set up
incorrectly. All the records should be in the same table with something like
a Hospital field with Mt. Sinai, Tufts, and UAB as the data.

 

[AGOKP4]

Hi Jerry,

Thanks for the reply. The 3 queries are coming from 3 different tables, its
the same type of data but from 3 centers, i couldn't figure how to combine
them...so I just update each table as I get the data then run the queries.
I'd be happy to know if there is a more efficient way of doing this.

I didn't understand the second part of your answer...the "the Count and
Group By. Last turn it into a crosstab" section. Do I use a single crosstab
query or?

Thanks

 

[AGOKP4]

I tried this...but it didnt work


TRANSFORM Count([Screened Cr by Teno ALL].[Study ID]) AS [CountOfStudy ID]
SELECT [Screened Cr by Teno ALL].[Creatinine Group], [Screened Cr by Teno
ALL].Sex, Count([Screened Cr by Teno ALL].[Creatinine Group]) AS
[CountOfCreatinine Group]
FROM [Screened Cr by Teno ALL]
GROUP BY [Screened Cr by Teno ALL].[Creatinine Group], [Screened Cr by Teno
ALL].Sex
PIVOT [Screened Cr by Teno ALL].Tenofovir;

 

[Duane Hookom]

Create a union query of your individual select queries first:

SELECT [Study ID], [Creatinine Group], [Sex], Tenofovir
FROM [Screened Qry Mt Sinai]
UNION ALL
SELECT [Study ID], [Creatinine Group], [Sex], Tenofovir
FROM [Screened Qry Tufts]
UNION ALL
SELECT [Study ID], [Creatinine Group], [Sex], Tenofovir
FROM [Screened Qry UAB];

Then create a crosstab from the result.

 

[Jeff Boyce]

Are you saying that you use one table per "center"? That's how you might do
it with a spreadsheet, but Access is a relational database.

Why not use a single table with all the (common) fields, then add one more
field to hold "center"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AGOKP4]

Thanks..this helped

Create a union query of your individual select queries first:

SELECT [Study ID], [Creatinine Group], [Sex], Tenofovir
FROM [Screened Qry Mt Sinai]
UNION ALL
SELECT [Study ID], [Creatinine Group], [Sex], Tenofovir
FROM [Screened Qry Tufts]
UNION ALL
SELECT [Study ID], [Creatinine Group], [Sex], Tenofovir
FROM [Screened Qry UAB];

Then create a crosstab from the result.

--
Duane Hookom
Microsoft Access MVP

Hi,

Is it possible to combine these queries below to get a single one?

Thanks!!!

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

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

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