This is the best I can do. Maybe you can take it and polish it some. It
uses 4 queries --
DataTbl_1 ---
TRANSFORM First(DataTbl.Hours) AS FirstOfHours
SELECT EmployeeTbl.EmpNo, EmployeeTbl.Employee
FROM EmployeeTbl INNER JOIN DataTbl ON EmployeeTbl.EmpNo = DataTbl.Employee
WHERE (((DataTbl.Date) Between [Forms].[CriteriaFrm].[txtStartDate] And
[Forms].[CriteriaFrm].[txtEndDate]))
GROUP BY EmployeeTbl.EmpNo, EmployeeTbl.Employee, DataTbl.Date
PIVOT Format([Date],"d") In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
DataTbl_2 ---
TRANSFORM First(DataTbl.Code) AS FirstOfCode
SELECT EmployeeTbl.EmpNo, DataTbl.Employee
FROM EmployeeTbl INNER JOIN DataTbl ON EmployeeTbl.EmpNo = DataTbl.Employee
WHERE (((DataTbl.Date) Between [Forms].[CriteriaFrm].[txtStartDate] And
[Forms].[CriteriaFrm].[txtEndDate]))
GROUP BY EmployeeTbl.EmpNo, DataTbl.Employee
PIVOT Format([Date],"d") In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
DataTbl_3 ---
SELECT "A" AS [A], DataTbl_1.EmpNo, DataTbl_1.Employee, DataTbl_1.[1],
DataTbl_1.[2], DataTbl_1.[3], DataTbl_1.[4], DataTbl_1.[5], DataTbl_1.[6],
DataTbl_1.[7], DataTbl_1.[8], DataTbl_1.[9], DataTbl_1.[10], DataTbl_1.[11],
DataTbl_1.[12], DataTbl_1.[13], DataTbl_1.[14], DataTbl_1.[15]
FROM DataTbl_1
UNION ALL SELECT "B" AS [A], DataTbl_2.EmpNo, DataTbl_2.Employee,
DataTbl_2.[1], DataTbl_2.[2], DataTbl_2.[3], DataTbl_2.[4], DataTbl_2.[5],
DataTbl_2.[6], DataTbl_2.[7], DataTbl_2.[8], DataTbl_2.[9], DataTbl_2.[10],
DataTbl_2.[11], DataTbl_2.[12], DataTbl_2.[13], DataTbl_2.[14], DataTbl_2.[15]
FROM DataTbl_2;
DataTbl_4 ---
SELECT DataTbl_3.EmpNo, DataTbl_3.Employee, DataTbl_3.[1], DataTbl_3.[2],
DataTbl_3.[3], DataTbl_3.[4], DataTbl_3.[5], DataTbl_3.[6], DataTbl_3.[7],
DataTbl_3.[8], DataTbl_3.[9], DataTbl_3.[10], DataTbl_3.[11], DataTbl_3.[12],
DataTbl_3.[13], DataTbl_3.[14], DataTbl_3.[15]
FROM DataTbl_3
ORDER BY DataTbl_3.EmpNo, DataTbl_3.A, DataTbl_3.Employee;
--
KARL DEWEY
Build a little - Test a little
Robbie Doo said:
Because I don't want SUM. I need individual daily Hour that is posted for the
employee.
This is what the report should look like:
Day1 2 3
4 .....15
------------------------------------------------------------------------------------
Employee Name 099 110 110 099
....099
Employee Number 8.0 8.0 8.0 8.0
.....8.0
------------------------------------------------------------------------------------
Employee Name 099 099 099 099
....099
Employee Number 8.0 8.0 8.0 8.0
.....8.0
------------------------------------------------------------------------------------
Where 099 or 110 are the Absence Codes and underneath are the hours.
However this information should be pulled for date criteria (betwee...and)
KARL DEWEY said:
Your query results will be nothing like the results you wanted in your
orignal post.
Why are you using FIRST instead of SUM?
Post sample data.
--
KARL DEWEY
Build a little - Test a little
:
I used a sub-report for the codes and inserted in the main report.
:
Your orignal post wanted 'each day in a column with absence codes and the
hours:'
Your SQL does not have a field that I can figure to have the 'absence
codes.' What is it?
--
KARL DEWEY
Build a little - Test a little
:
Karl, here's the underlying SQL:
TRANSFORM First(DataTbl.Hours) AS FirstOfHours
SELECT Year([Date]) AS TheYear, Month([Date]) AS TheMonth,
EmployeeTbl.Employee, EmployeeTbl.Supervisor, DataTbl.Date, DataTbl.Hours,
First(EmployeeTbl.EmpNo) AS FirstOfEmpNo
FROM EmployeeTbl INNER JOIN DataTbl ON EmployeeTbl.EmpNo = DataTbl.Employee
WHERE (((DataTbl.Date) Between [Forms].[CriteriaFrm].[txtStartDate] And
[Forms].[CriteriaFrm].[txtEndDate]))
GROUP BY Year([Date]), Month([Date]), EmployeeTbl.Employee,
EmployeeTbl.Supervisor, DataTbl.Date, DataTbl.Hours
PIVOT Day([Date]) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
:
Try this where EmpNUM is absence code and Emp# is hours absence --
TRANSFORM Sum([Robbie Doo].[Emp#]) AS [SumOfEmp#]
SELECT [Employee] & " - " & [EmpNUM] AS [Employee - Absence]
FROM [Robbie Doo]
GROUP BY [Employee] & " - " & [EmpNUM]
PIVOT Format([Absence],"d");
--
KARL DEWEY
Build a little - Test a little
:
Post the SQL of your crosstab.
--
KARL DEWEY
Build a little - Test a little
:
Is it possible to make a report in Access that would list the days from 1 to
15 or 16 to 30/31 each day in a column with absence codes and the hours:
1 2 3 4
EmpNm 099 110 099 099
Emp# 8.0 8.0 8.0 8.0
I tried this with crosstab queries and came up with the report but I don't
get the data in the right columns. Instead of getting the data next to each
other I get the next day on another line with the employee name & # repeated.
Any help?
