Problem with Challenging Very Complicated Query

  • Thread starter Thread starter Ker
  • Start date Start date
K

Ker

Attached is my query. The query itself works great. I get data on my
report such as

Angie Watkins (Name)
Date day total
9/2/03 Tues 25
9/2/03 Tues 16
9/3/03 Wed 18

But I need 9/2 data summed such as
9/2/03 Tues 41
9/3/03 Wed 18

Per project specifications, I must store all start and stop times and
use date calculations. Can anyone look at my query and see if you can
figure out how to sum this data so it appears correct on my report.
Thanks in advance. Ker

SELECT

tblEmployee.EmployeeStatus, tblEmployee.EmployeeName,
SubJobTable.Date, Format$([SubJobTable.Date],"ddd") AS [Day],
Format$([SubJobTable.Date],"Short Date",0,0) AS Fordate,
SubJobTable.GLAcct, (([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation]+[AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation]-[LunchToBeDeducted])/60)
AS TotalWithBenefits,
IIf(IsNull(([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation]+[AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation]-[LunchToBeDeducted])/60),0,([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation]+[AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation
-[LunchToBeDeducted])/60) AS Total,
([JobCalculation]+[LoadUpCalculation]+[TravelCalculation]+[PlantMatPUDelCalculation]+[DesignCalculation])
AS TotalBillable, ([AdminCalculation]+[ShopCalculation]+[NurseryCalculation]+[OtherCalculation])
AS TotalNonBillable,
IIf(IsNull(Sum(DateDiff("n",[JobStart],[JobEnd]))),0,(Sum(DateDiff("n",[JobStart],[JobEnd]))))
AS JobCalculation, IIf(IsNull(Sum(DateDiff("n",[LoadUpStart],[LoadUpEnd]))),0,(Sum(DateDiff("n",[LoadUpStart],[LoadUpEnd]))))
AS LoadUpCalculation, ([TravelFromCalculation]+[TravelToCalculation])
AS TravelCalculation,
IIf(IsNull(Sum(DateDiff("n",[PlantMatPUDelStart],[PlantMatPUDelEnd]))),0,(Sum(DateDiff("n",[PlantMatPUDelStart],[PlantMatPUDelEnd]))))
AS PlantMatPUDelCalculation,
IIf(IsNull(Sum(DateDiff("n",[DesignStart],[DesignEnd]))),0,(Sum(DateDiff("n",[DesignStart],[DesignEnd]))))
AS DesignCalculation,
IIf(IsNull(Sum(DateDiff("n",[TravelFromStart],[TravelFromEnd]))),0,(Sum(DateDiff("n",[TravelFromStart],[TravelFromEnd]))))
AS TravelFromCalculation,
IIf(IsNull(Sum(DateDiff("n",[TravelToStart],[TravelToEnd]))),0,(Sum(DateDiff("n",[TravelToStart],[TravelToEnd]))))
AS TravelToCalculation,
IIf(IsNull(Sum(DateDiff("n",[AdminStart],[AdminEnd]))),0,(Sum(DateDiff("n",[AdminStart],[AdminEnd]))))
AS AdminCalculation,
IIf(IsNull(Sum(DateDiff("n",[ShopStart],[ShopEnd]))),0,(Sum(DateDiff("n",[ShopStart],[ShopEnd]))))
AS ShopCalculation, IIf(IsNull(Sum(DateDiff("n",[NurseryStart],[NurseryEnd]))),0,(Sum(DateDiff("n",[NurseryStart],[NurseryEnd]))))
AS NurseryCalculation,
IIf(IsNull(Sum(DateDiff("n",[OtherStart],[OtherEnd]))),0,(Sum(DateDiff("n",[OtherStart],[OtherEnd]))))
AS OtherCalculation, IIf(([JobName]="Non-Job
Related"),[LunchToBeDeducted],0) AS LunchCalculationNonJob,
IIf(([JobName]="Non-Job Related"),0,[LunchToBeDeducted]) AS
LunchCalculationJob, SubJobTable.LunchToBeDeducted

FROM

SubJobTable RIGHT JOIN tblEmployee ON SubJobTable.EmployeeName =
tblEmployee.EmployeeName

WHERE

(((tblEmployee.EmployeeStatus)="clerical" Or
(tblEmployee.EmployeeStatus)="salary" Or (tblEmployee.EmployeeStatus)
Is Null) AND ((SubJobTable.Date) Between
[forms]![frmDateRange]![StartDate] And
[forms]![frmDateRange]![EndDate])) OR
(((tblEmployee.EmployeeStatus)="clerical" Or
(tblEmployee.EmployeeStatus)="salary" Or (tblEmployee.EmployeeStatus)
Is Null) AND ((IsNull([SubJobTable].[Date]))<>False))

GROUP BY

tblEmployee.EmployeeStatus,
tblEmployee.EmployeeName, SubJobTable.Date,
Format$([SubJobTable.Date],"ddd"), Format$([SubJobTable.Date],"Short
Date",0,0), SubJobTable.GLAcct, IIf(([JobName]="Non-Job
Related"),[LunchToBeDeducted],0), IIf(([JobName]="Non-Job
Related"),0,[LunchToBeDeducted]), SubJobTable.LunchToBeDeducted

ORDER BY

tblEmployee.EmployeeName;
 
Can anyone look at my query and see if you can
figure out how to sum this data so it appears correct on my report.
Click to expand...

I think you just need to remove SubJobtable.Date from both the SELECT
and GROUP BY clauses so that it will group by the formatted dates
rather than by the date/time value itself.

Note that Date is a reserved word, and downright misleading in this
case anyway since the field named Date is storing both a date and a
time; you should consider changing the fieldname!
 
Back
Top