G
Guest
I have a query that have some sum field that I want to display in the Report,
can ths be done?
The sql works now perfectly , but can the sum of hours is in blank .
Employee 1234 Pay Perio ending 12122004 (2 weeks)
activity 9999 12 hr (sum by employee by pay period) $ (amount for
12hr)activity 8888 28 hr
activity 3333 20 hr
activity 2222 20 hrs
heres is the sql
SELECT [DDS File PP24].EMPLID, [Employee Data CLEAN].[Preferred Name],
[Employee Data CLEAN].SSN, [Employee Data CLEAN].[Job Title], AC.DESCRIPTION,
AC.[Long Desc], [DDS File PP24].PAY_END_DT, [DDS File PP24].ACTIVITY,
Sum([DDS File PP24].HOURS) AS SumOfHOURS1, Sum([DDS File PP24].AMOUNT) AS
SumOfAMOUNT1, [Total Hrs Total Amount].SumOfHOURS, [Total Hrs Total
Amount].SumOfAMOUNT, [FY05 PPE Dates].Month, ([SumOfHOURS1]/[SumOfHOURS]) AS
[% Effort], ([SumOfAMOUNT1]/[SumOfAMOUNT]) AS [% Salary], "100%" AS [Total %
Effort], "100%" AS [Total % Salary]
FROM ((([DDS File PP24] LEFT JOIN [Total Hrs Total Amount] ON ([DDS File
PP24].EMPLID = [Total Hrs Total Amount].EMPLID) AND ([DDS File
PP24].PAY_END_DT = [Total Hrs Total Amount].PAY_END_DT)) LEFT JOIN AC ON [DDS
File PP24].ACTIVITY = AC.ACTIVITY) LEFT JOIN [FY05 PPE Dates] ON [DDS File
PP24].PAY_END_DT = [FY05 PPE Dates].[Pay Period Ending Date]) LEFT JOIN
[Employee Data CLEAN] ON [DDS File PP24].EMPLID = [Employee Data CLEAN].EmpID
GROUP BY [DDS File PP24].EMPLID, [Employee Data CLEAN].[Preferred Name],
[Employee Data CLEAN].SSN, [Employee Data CLEAN].[Job Title], AC.DESCRIPTION,
AC.[Long Desc], [DDS File PP24].PAY_END_DT, [DDS File PP24].ACTIVITY, [Total
Hrs Total Amount].SumOfHOURS, [Total Hrs Total Amount].SumOfAMOUNT, [FY05 PPE
Dates].Month, ([SumOfHOURS1]/[SumOfHOURS]), ([SumOfAMOUNT1]/[SumOfAMOUNT]),
"100%", "100%"
HAVING ((([DDS File PP24].PAY_END_DT) Like [Enter Pay Period Ending Date])
AND (([DDS File PP24].ACTIVITY) Is Not Null));
can ths be done?
The sql works now perfectly , but can the sum of hours is in blank .
Employee 1234 Pay Perio ending 12122004 (2 weeks)
activity 9999 12 hr (sum by employee by pay period) $ (amount for
12hr)activity 8888 28 hr
activity 3333 20 hr
activity 2222 20 hrs
heres is the sql
SELECT [DDS File PP24].EMPLID, [Employee Data CLEAN].[Preferred Name],
[Employee Data CLEAN].SSN, [Employee Data CLEAN].[Job Title], AC.DESCRIPTION,
AC.[Long Desc], [DDS File PP24].PAY_END_DT, [DDS File PP24].ACTIVITY,
Sum([DDS File PP24].HOURS) AS SumOfHOURS1, Sum([DDS File PP24].AMOUNT) AS
SumOfAMOUNT1, [Total Hrs Total Amount].SumOfHOURS, [Total Hrs Total
Amount].SumOfAMOUNT, [FY05 PPE Dates].Month, ([SumOfHOURS1]/[SumOfHOURS]) AS
[% Effort], ([SumOfAMOUNT1]/[SumOfAMOUNT]) AS [% Salary], "100%" AS [Total %
Effort], "100%" AS [Total % Salary]
FROM ((([DDS File PP24] LEFT JOIN [Total Hrs Total Amount] ON ([DDS File
PP24].EMPLID = [Total Hrs Total Amount].EMPLID) AND ([DDS File
PP24].PAY_END_DT = [Total Hrs Total Amount].PAY_END_DT)) LEFT JOIN AC ON [DDS
File PP24].ACTIVITY = AC.ACTIVITY) LEFT JOIN [FY05 PPE Dates] ON [DDS File
PP24].PAY_END_DT = [FY05 PPE Dates].[Pay Period Ending Date]) LEFT JOIN
[Employee Data CLEAN] ON [DDS File PP24].EMPLID = [Employee Data CLEAN].EmpID
GROUP BY [DDS File PP24].EMPLID, [Employee Data CLEAN].[Preferred Name],
[Employee Data CLEAN].SSN, [Employee Data CLEAN].[Job Title], AC.DESCRIPTION,
AC.[Long Desc], [DDS File PP24].PAY_END_DT, [DDS File PP24].ACTIVITY, [Total
Hrs Total Amount].SumOfHOURS, [Total Hrs Total Amount].SumOfAMOUNT, [FY05 PPE
Dates].Month, ([SumOfHOURS1]/[SumOfHOURS]), ([SumOfAMOUNT1]/[SumOfAMOUNT]),
"100%", "100%"
HAVING ((([DDS File PP24].PAY_END_DT) Like [Enter Pay Period Ending Date])
AND (([DDS File PP24].ACTIVITY) Is Not Null));