Duplicates in Pivot Query 1

[rewdee]

I have a table that has an autoID, ForeignKey, ShiftID,EmpID,StartTime,EndTime and HrsWrked as shown below:

I have an initial query to make the pivot query simpler as:

SELECT (Employees.FirstName+' '+Employees.LastName) AS FullName, tempBatch.ShiftDate, (Format(tempBatch.StartTime,"h:n AM/PM")+' to '+Format(tempBatch.EndTime,"h:n AM/PM")) AS WorkHrs, tempBatch.ShiftWeekID, tempBatch.TimeWrked, Employees.EmployeeID
FROM tempBatch INNER JOIN Employees ON tempBatch.EmpID=Employees.EmployeeID
ORDER BY Employees.EmployeeID, tempBatch.ShiftDate;

Then I have a pivot query as:

PARAMETERS [paramShiftID] Long;
TRANSFORM First(qryTempWrkSchedule.WorkHrs) AS WorkHrs
SELECT qryTempWrkSchedule.FullName, sum(TimeWrked) AS TotalHours
FROM qryTempWrkSchedule
WHERE ShiftWeekID=[paramShiftID]
GROUP BY qryTempWrkSchedule.FullName, TimeWrked
ORDER BY FullName
PIVOT Format([ShiftDate],"Short Date");

However the results are strange. I'm getting some duplicates as shown below:

I don't know how or why but the additions appear to be later inserts into the table.

If anyone has any ideas on how to fix this, I will be greatly indebted to he/she. If anyone wants a copy of the access file I'll email them.

Thanks,
Rewdee

 

[dhookom]

Try get rid of the TimeWrked group by:

PARAMETERS [paramShiftID] Long;
TRANSFORM First(qryTempWrkSchedule.WorkHrs) AS WorkHrs
SELECT qryTempWrkSchedule.FullName, sum(TimeWrked) AS TotalHours
FROM qryTempWrkSchedule
WHERE ShiftWeekID=[paramShiftID]
GROUP BY qryTempWrkSchedule.FullName[s][red], TimeWrked[/red][/s]
ORDER BY FullName
PIVOT Format([ShiftDate],"Short Date");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rewdee]

Thanks Duane -- I owe you one.

Rewdee