Sum in Union Query

[beckysright]

I am fairly new to writing in SQL and I have a union query that I can't seem to get right. I know it is possible but I can't seem to make it work for me. I need to add together the TotalDays by EmpName. I also need to keep the week ending in there. I would really appreciate it if anyone could tell me how to do this? Thank you!



SELECT
IIf([EmployeeComm]=1111,"Name1",IIf([EmployeeComm]=2222,"Name2",IIf([EmployeeComm]=2553,"Name3",IIf([EmployeeComm]=2923,"Name4",IIf([EmployeeComm]=3333,"Name5",IIf([EmployeeComm]=4444,"Name6",IIf([EmployeeComm]=4807,"Name7",IIf([EmployeeComm]=9999,"Name8","Null")))))))) AS EmpName, HoursWorked.WeekEnding, HoursWorked.NoDaysWorked AS TotalDays
FROM Employees INNER JOIN (HospitalBillingLevels INNER JOIN (PhysicianAgreements INNER JOIN (HoursWorked INNER JOIN InsuranceChargeperInvoice ON HoursWorked.InvNo = InsuranceChargeperInvoice.InvNo) ON PhysicianAgreements.PhysDealNo = HoursWorked.PhysAgreement) ON HospitalBillingLevels.HospitalAgreement = HoursWorked.HospAgreement) ON Employees.EmpID = HospitalBillingLevels.EmployeeCommis
WHERE (((HoursWorked.WeekEnding)=#7/13/2013#))
UNION ALL
SELECT IIf([EmployeeCommis]=1111,"Name1",IIf([EmployeeCommis]=2222,"Name2",IIf([EmployeeCommis]=2553,"Name3",IIf([EmployeeCommis]=2923,"Name4",IIf([EmployeeCommis]=3333,"Name5",IIf([EmployeeCommis]=4444,"Name6",IIf([EmployeeCommis]=4807,"Name7",IIf([EmployeeCommis]=9999,"Name8","Null")))))))) AS EmpName, HoursWorked.WeekEnding, HoursWorked.NoDaysWorked AS TotalDays
FROM Employees INNER JOIN (HospitalBillingLevels INNER JOIN (PhysicianAgreements INNER JOIN (HoursWorked INNER JOIN InsuranceChargeperInvoice ON HoursWorked.InvNo = InsuranceChargeperInvoice.InvNo) ON PhysicianAgreements.PhysDealNo = HoursWorked.PhysAgreement) ON HospitalBillingLevels.HospitalAgreement = HoursWorked.HospAgreement) ON Employees.EmpID = HospitalBillingLevels.EmployeeCommis
WHERE (((HoursWorked.WeekEnding)=#7/13/2013#));

[June7]

Don't understand why you are doing a UNION of two identical queries.

If you want aggregate calcs, then do a GROUP BY (Totals) query or build a report using Grouping & Sorting with aggregate calcs in header/footer sections.

[beckysright]

It looks very similar but the Joins are different. It shows the number of days worked on the physician commission side and the hospital side. The only difference is where the names are connected.... but they are different.

I have tried to do groups prior to the union query and it didn't work.

[June7]

I have read and compared both SELECT queries letter by letter several times and the joins are the same. The only difference is the field referenced in the IIf expression. Why are you hard coding ID's and names? Is there not a table that relates EmployeeComm and EmployeeCommis numbers with names? Which tables are these fields in?

Why is it not working, what happens - error message, wrong results?

Did you try a report? It will allow display of detail data as well as aggregate calcs.