...and it's returning duplicates. I need to see the amount to pay the worker every two weeks. So I need the total the worker is paid for an account (company table), the sum of any extras the worker did - like carpet cleaning (Extras Table) and the workers name and the workers company name (Crew Table). It should return 46 records when all is summed (i.e. 46 workers). I am getting 31 and 8 of the records show up 2 or more times.
here is the querie in SQL
SELECT CrewInformation.CrewMemberId, CrewInformation.CrewSupervisorName, CrewInformation.CrewCompanyName, Sum(CompanyInformation.CrewMemberAmount) AS SumOfCrewMemberAmount, Sum(tblCompanyExtras.ExtraWorkerAmt) AS SumOfExtraWorkerAmt
FROM (CrewInformation LEFT JOIN CompanyInformation ON CrewInformation.CrewMemberId = CompanyInformation.CompanyCrewMember) LEFT JOIN tblCompanyExtras ON CrewInformation.CrewMemberId = tblCompanyExtras.ExtraWorkId
WHERE (((CompanyInformation.CompanyActive)=True) AND ((CrewInformation.CrewMemberActive)=True) AND ((DatePart("d",[extradate]))>=1 And (DatePart("d",[extradate]))<=15) AND ((Format([extradate],"mm/yyyy")) Like "07/2011"))
GROUP BY CrewInformation.CrewMemberId, CrewInformation.CrewSupervisorName, CrewInformation.CrewCompanyName, tblCompanyExtras.ExtraDate;
I've tried splitting this into two tables and not summing - thinking i could do it in the report. Those solutions aren't working.