Crosstab report

[kimsMTC]

I have a created a crosstab feeder query that joins my tables together, then created a cross table query that is used by a crosstab report. My problem is that my totals are incorrect and I cannot even figure out where the value being displayed is coming from. Please help!

Feeder Query:
SELECT Department.DepartmentName, ProjectType.ProjectTypeAbbrev, Time.WorkDate, Time.Hours, [LastName] & ", " & [FirstName] AS FullName, Employee.EmployeeNumber
FROM ProjectType INNER JOIN (Department INNER JOIN (Employee INNER JOIN (Project INNER JOIN [Time] ON Project.ProjectID = Time.ProjectID) ON Employee.EmployeeID = Time.EmployeeID) ON Department.DepartmentID = Employee.DepartmentID) ON ProjectType.ProjectTypeID = Project.ProjectTypeID
WHERE (((ProjectType.ProjectTypeAbbrev) Is Not Null) AND ((Time.WorkDate) Between [Forms]![PayrollPrompt]![DtPeriodStarting] And [Forms]![PayrollPrompt]![DtPeriodEnding]));

Crosstab Query:
PARAMETERS [Forms]![PayrollPrompt]![DtPeriodStarting] DateTime, [Forms]![PayrollPrompt]![DtPeriodEnding] DateTime;
TRANSFORM Sum(PayrollFeederQuery.Hours) AS SumOfHours
SELECT PayrollFeederQuery.DepartmentName, PayrollFeederQuery.EmployeeNumber, PayrollFeederQuery.FullName, Sum(PayrollFeederQuery.Hours) AS TotalHours
FROM PayrollFeederQuery
GROUP BY PayrollFeederQuery.DepartmentName, PayrollFeederQuery.EmployeeNumber, PayrollFeederQuery.FullName
PIVOT PayrollFeederQuery.ProjectTypeAbbrev In ("REG","OVT","HDY","VCN","JRY","BRE","STD","SEV");


Thanks!

Kim

 

[lameid]

Wrong how?

The queries seem like they would be right to me. You might try making a report off the feeder query as a comparison. It will help figure out WHERE the problem is.

Also, next time, please post reports questions in the Access Reports forum.

 

[danvlas]

Just a thought: what date format are you using?
Maybe they are evaluated incorrectly.

Try mm/dd/yyyy in Regional Settings on your computer, if that's the cause,post back.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[kimsMTC]

Sorry for the mis-posting. I determined my problem was in the data. The report I was ask to create on the crosstab did not include all the possible column types. Therefore, the summing was adding ALL time records, but the individual columns were adding on those I specified in the query. The way Access handles crosstabs is confusing to me. Harder than it should be...anyway, thank you for your input.

Kim