3 quries in 1 table

[sgab]

i have 3 quiries built for 3 audits(p-mortaaa, p-mortcea, p-mortamp).

each quiry shows the same kind of data for the 3 different audits

I need 1 report that shows all 3 results, but dont know how to do that. i could open a new report, but it only lets me choose 1 query.

any ideas?

 

[SkipVought]

Hi,


Posibly a UNION query

Select field1, field2, ..., 'Audit1' As 'Which Audit' From...
UNION
Select field1, field2, ..., 'Audit2' From...
UNION
Select field1, field2, ..., 'Audit3' From...

Skip,
[sub]
[red][/red]
[/sub]

 

[rjoubert]

Assuming all 3 queries have the same exact fields, you could create a UNION query, and base your report on that. OR, you could create subreports in your report.

 

[sgab]

sorry i should of explained, im fairly new to access, and have never heard of Union or sub report, ?

how do i do this?

thanks

 

[SkipVought]

Please post the SQL for your 3 queries.

Skip,
[sub]
[red][/red]
[/sub]

 

[sgab]

this id for p-mortaaa

SELECT aaa.Name, aaa.Priority, Count(*) AS CountOfDied
FROM aaa
WHERE (((aaa.thirtydaymortality)="Dead"))
GROUP BY aaa.Name, aaa.Priority;


and p-mortamp

SELECT amp.Name, amp.Priority, Count(*) AS CountOfDied
FROM amp
WHERE (((amp.thirtydaymortality)="Dead"))
GROUP BY amp.Name, amp.Priority;


and p-mortcea

SELECT CEA.Name, CEA.Priority, Count(*) AS CountOfDied
FROM CEA
WHERE (((CEA.thirtydaymortality)="Dead"))
GROUP BY CEA.Name, CEA.Priority;

 

[SkipVought]

SELECT aaa.Name, aaa.Priority, Count(*) AS CountOfDied, 'p-mortaaa' As 'Audit'
FROM aaa
WHERE (((aaa.thirtydaymortality)="Dead"))
GROUP BY aaa.Name, aaa.Priority
UNION
SELECT amp.Name, amp.Priority, Count(*) AS CountOfDied, 'p-mortamp'
FROM amp
WHERE (((amp.thirtydaymortality)="Dead"))
GROUP BY amp.Name, amp.Priority
UNION
SELECT CEA.Name, CEA.Priority, Count(*) AS CountOfDied, 'p-mortcea'
FROM CEA
WHERE (((CEA.thirtydaymortality)="Dead"))
GROUP BY CEA.Name, CEA.Priority;

Skip,
[sub]
[red][/red]
[/sub]