Date() and Sum in query

[leahb747]

I have a contractor timesheet system in which timesheets are manually entered and reports are run based on these. What I want to do is be able to show on a report the Week to Date costs inccured.

In a query, I have assigned each timesheet entry a Week No (1-52), Year No and Month No. What I want the below code to say in the query is to sum the costs of each timesheet where the value in the Week column is equal to the DatePart "ww" (week), for today's date. Ie, I have three timesheets that are entered for yesterday and one for monday. I want the costs on all four to be registered as they are in the same week as today. The code I have is as follows. It keeps giving me the error that I tried to execute a query that does not include ContractDesc in the aggregate function.

TWCost: Sum(IIf([qryIncurredCosts.Week]=(DatePart("ww",[Date()])),"[Cost]","0"))

Ideas? Questions? Comments?

TIA

Leah

 

[recyan]

Perhaps, this could be the case ( at least initially ):

SELECT 
	a, 
	b,
	SUM(c) AS sumOfc 
FROM 
	myTable 
GROUP BY	
	a

This will throw an error similar to the one you have,
as "b" is not included in the GROUP BY clause.

The GROUP BY expects all fields that are there in the SELECT clause ( rightly so, I think), except those on which aggregate functions are being carried out.

Post your full SQL for the query, some one should be able to get you going.

Thanks

 

[leahb747]

It currently looks like this...

SELECT qryIncurredCosts.Contract, qryIncurredCosts.ContractDesc, qryIncurredCosts.Contractor, qryIncurredCosts.Representative, qryIncurredCosts.AwardDate, qryIncurredCosts.DayworkNo, qryIncurredCosts.DayDate, qryIncurredCosts.FacilityCode, qryIncurredCosts.Item, qryIncurredCosts.ResourceID, qryIncurredCosts.ResourceDesc, qryIncurredCosts.ResourceStart, qryIncurredCosts.ResourceFinish, qryIncurredCosts.ResourceCurrent, qryIncurredCosts.ApplicableRateCode, qryIncurredCosts.RateCodeDesc, qryIncurredCosts.Unit, qryIncurredCosts.OperatingRate, qryIncurredCosts.StandbyRate, qryIncurredCosts.BreakdownRate, qryIncurredCosts.LabPlant, qryIncurredCosts.RateStart, qryIncurredCosts.RateFinish, qryIncurredCosts.RateCurrent, qryIncurredCosts.Quantity, qryIncurredCosts.OperatingCost, qryIncurredCosts.BreakdownCost, qryIncurredCosts.StandbyCost, qryIncurredCosts.Cost, IIf([DayDate] Between [Forms]![frmReportsSwitchboard]![StartDate] And [Forms]![frmReportsSwitchboard]![EndDate],[Quantity],0) AS TPQty, IIf([Daydate] Between [Forms]![frmReportsSwitchboard]![StartDate] And [Forms]![frmReportsSwitchboard]![EndDate],[Cost],0) AS TPCost, qryIncurredCosts.TradeCommodityCode, qrepCMSWBS.FacilityDescription, qrepCMSWBS.PhaseCode, qryIncurredCosts.Week, qryIncurredCosts.Month, qryIncurredCosts.Year, Sum(IIf([qryIncurredCosts.Week]=(DatePart("ww",[Date()])),"[Cost]","0")) AS TWCost
FROM qryIncurredCosts INNER JOIN qrepCMSWBS ON (qryIncurredCosts.TradeCommodityCode = qrepCMSWBS.TradeCommodityCode) AND (qryIncurredCosts.FacilityCode = qrepCMSWBS.FacilityCode) AND (qryIncurredCosts.Contract = qrepCMSWBS.PackageNumber)
GROUP BY qryIncurredCosts.Contract;

 

[leahb747]

All good.

Problem solved.

Added in a few extra steps to get the query working