Grouping a Union Query 1

[lars7]

Hi Guys,
I'm having problems with a union query. I can't get it to group in the way that I want, here is the sql of the 2 queries:

QueryMainReport1

SELECT QueryRecordSource.Group, tblStaffGroup.StaffGroup, Count(QueryRecordSource.[Pay Number]) AS Staff, Sum(IIf(tblKSF.KSF,1,0)) AS KSF1, Sum(IIf(tblKSF.Validated,1,0)) AS KSF2, Sum(IIf(tblKSF.PDP,1,0)) AS PDP1
FROM tblRecSource INNER JOIN ((QueryRecordSource LEFT JOIN tblKSF ON QueryRecordSource.[Pay Number] = tblKSF.[Pay Number]) LEFT JOIN tblStaffGroup ON QueryRecordSource.Discipline = tblStaffGroup.StaffGroup_Abb) ON tblRecSource.ServiceCode = QueryRecordSource.Group
GROUP BY QueryRecordSource.Group, tblStaffGroup.StaffGroup;

and QueryMainReport2

SELECT tblKSF.KSFGroup AS [Group], tblStaffGroup.StaffGroup, Count(QueryRecordSource.[Pay Number]) AS Staff, Sum(IIf(tblKSF.KSF,1,0)) AS KSF1, Sum(IIf(tblKSF.Validated,1,0)) AS KSF2, Sum(IIf(tblKSF.PDP,1,0)) AS PDP1
FROM (QueryRecordSource LEFT JOIN tblKSF ON QueryRecordSource.[Pay Number] = tblKSF.[Pay Number]) LEFT JOIN tblStaffGroup ON QueryRecordSource.Discipline = tblStaffGroup.StaffGroup_Abb
GROUP BY tblKSF.KSFGroup, tblStaffGroup.StaffGroup, QueryRecordSource.Group
HAVING (((tblKSF.KSFGroup)<>[Group]));

And I'm unioning them with:

select [Group], [StaffGroup], [Staff], [KSF1], [KSF2], [PDP1]
From QueryMainReport1

UNION ALL select [Group], [StaffGroup], [Staff], [KSF1], [KSF2], [PDP1]
From QueryMainReport2
Order By [Group];

with this result:

Group StaffGroup Staff KSF1 KSF2PDP1
West Dunbartonshire Chp ANCILLARY 88 29 27 22
West Dunbartonshire Chp MAINTENANCE 1 0 0 0
West Dunbartonshire Chp NURSING(TR) 309 210 200 129
West Dunbartonshire Chp NURSING(UNTR) 16 6 6 2
West Dunbartonshire Chp OPTOMETRISTS 1 0 0 0
West Dunbartonshire Chp PHARMACY 4 2 0 0
West Dunbartonshire Chp PHARMACY 2 2 0 0
West Dunbartonshire Chp NURSING(TR) 1 1 1 1
West Dunbartonshire Chp NURSING(TR) 7 7 7 6
West Dunbartonshire Chp TECHNICALS 1 1 0 0
West Dunbartonshire Chp NURSING(TR) 1 1 1 1
West Dunbartonshire Chp AHP'S 2 2 2 2
West Dunbartonshire Chp AHP'S 28 28 17 9
West Dunbartonshire Chp NURSING(TR) 3 3 3 1

This all works well but it does not Group the [Groups] together as I need them to be.

Can this be done.

Thanks in advance.

 

[PHV]

You may try this:

SELECT [Group], StaffGroup, Sum(Staff), Sum(KSF1), Sum(KSF2), Sum(PDP1)
FROM (
select  [Group],  [StaffGroup], [Staff], [KSF1], [KSF2], [PDP1]
From QueryMainReport1
UNION ALL select  [Group], [StaffGroup], [Staff], [KSF1], [KSF2], [PDP1]
From QueryMainReport2
) AS U
GROUP BY [Group], StaffGroup
ORDER BY [Group];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Thanks PH, it works great.

 

[lars7]

Hi PH,

I spoke too soon, when I tried to open it again I got an error message "Invalid bracketing of name 'select[Group"

any Ideas?

 

[PHV]

Create a new query named, say, QueryMainReport1and2:
select [Group], [StaffGroup], [Staff], [KSF1], [KSF2], [PDP1]
From QueryMainReport1
UNION ALL select [Group], [StaffGroup], [Staff], [KSF1], [KSF2], [PDP1]
From QueryMainReport2

and now, your final query:
SELECT [Group], StaffGroup, Sum(Staff), Sum(KSF1), Sum(KSF2), Sum(PDP1)
FROM QueryMainReport1and2
GROUP BY [Group], StaffGroup
ORDER BY [Group]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Thanks again, the extra query seem to have done the trick.