D
DOMIRICAN
I'm not sure how to approach this, but I need some advice on either changing
the layout of my database or maybe a wayout to acomplish what I need.
I need to create a report that looks like a crosstab/spreadsheet query.
The field in my database is as follows:
ID PDate Start Stop Item 1 2 3 4 5 6
7 8 9 10 B1 B2 L
8 10/11/2007 7:00 AM 3:30 PM 12 7
11 10/11/2007 7:00 AM 3:30 PM 11 100 108
The ID are employee's id with a table associated with employee names and dept.
PDate is the date the production was measured along with the start time and
the stop time
The Item are actual linen items with a table associated with the linen
description and other pertinant info.
The numbers 1-10 represent the hours of the day being that productions is
measureD by the hour and the most they will work is a 10 hour period.
B1, B2 are breaks normally 15 minutes and L is the lunch normally 30 min.
Based on this information, I need to create a report to look like a crosstab
with the following info:
Employee Sun dd/yy Mon dd/yy Tue dd/yy Wed dd/yy Thu
dd/yy Fri dd/yy Sat
under the days I need the production % for each day.
Here is how I calculate the percentage:
I have a query named ProductionQueryrpt which does a lot of the calculation
SELECT DISTINCT Employee.Department, [Daily Production].ID, [Daily Production]
..PDate, [Daily Production].Start, [Daily Production].Stop, [Daily Production].
Item, [Daily Production].[1], [Daily Production].[2], [Daily Production].[3],
[Daily Production].[4], [Daily Production].[5], [Daily Production].[6],
[Daily Production].[7], [Daily Production].[8], [Daily Production].[9],
[Daily Production].[10], [Daily Production].B1, [Daily Production].B2, [Daily
Production].L, (nz([1])+nz([2])+nz([3])+nz([4])+nz([5])+nz([6])+nz([7])+nz([8]
)+nz([9])+nz([10])) AS Ptotal, DLookUp("[std hr]","item standard","item=" &
[item]) AS Std, IIf([std]=0,0,([ptotal]/[std])) AS [Earned Hrs], IIf([item]
=24,[ptotal],0) AS Station, IIf([item]=25,[ptotal],0) AS project, DateDiff
("n",[START],IIf([STOP]<[START],DateAdd("d",1,[STOP]),[STOP]))/60 AS TOTALHR,
Employee.Employee, NZ([B1]+[B2])/60 AS Break
FROM [Daily Production] INNER JOIN Employee ON [Daily Production].ID =
Employee.ID
WHERE ((([Daily Production].PDate)=[Enter start date]));
then on the actual report I have the following:
Percentage =([totalearnedhr]/([tpaid]-([tproject]+[tstation]+[break])))
TotalEarnedHrs = Sum([earned hrs])
tpaid =([twork]-[Lunch])
tproject =Sum([project])/60
tstation =Sum([station])/60
break =Sum([Break])
lunch =Sum([l])/60
twork =[TOTALHR]/60
Need help!!! PLEASE
the layout of my database or maybe a wayout to acomplish what I need.
I need to create a report that looks like a crosstab/spreadsheet query.
The field in my database is as follows:
ID PDate Start Stop Item 1 2 3 4 5 6
7 8 9 10 B1 B2 L
8 10/11/2007 7:00 AM 3:30 PM 12 7
11 10/11/2007 7:00 AM 3:30 PM 11 100 108
The ID are employee's id with a table associated with employee names and dept.
PDate is the date the production was measured along with the start time and
the stop time
The Item are actual linen items with a table associated with the linen
description and other pertinant info.
The numbers 1-10 represent the hours of the day being that productions is
measureD by the hour and the most they will work is a 10 hour period.
B1, B2 are breaks normally 15 minutes and L is the lunch normally 30 min.
Based on this information, I need to create a report to look like a crosstab
with the following info:
Employee Sun dd/yy Mon dd/yy Tue dd/yy Wed dd/yy Thu
dd/yy Fri dd/yy Sat
under the days I need the production % for each day.
Here is how I calculate the percentage:
I have a query named ProductionQueryrpt which does a lot of the calculation
SELECT DISTINCT Employee.Department, [Daily Production].ID, [Daily Production]
..PDate, [Daily Production].Start, [Daily Production].Stop, [Daily Production].
Item, [Daily Production].[1], [Daily Production].[2], [Daily Production].[3],
[Daily Production].[4], [Daily Production].[5], [Daily Production].[6],
[Daily Production].[7], [Daily Production].[8], [Daily Production].[9],
[Daily Production].[10], [Daily Production].B1, [Daily Production].B2, [Daily
Production].L, (nz([1])+nz([2])+nz([3])+nz([4])+nz([5])+nz([6])+nz([7])+nz([8]
)+nz([9])+nz([10])) AS Ptotal, DLookUp("[std hr]","item standard","item=" &
[item]) AS Std, IIf([std]=0,0,([ptotal]/[std])) AS [Earned Hrs], IIf([item]
=24,[ptotal],0) AS Station, IIf([item]=25,[ptotal],0) AS project, DateDiff
("n",[START],IIf([STOP]<[START],DateAdd("d",1,[STOP]),[STOP]))/60 AS TOTALHR,
Employee.Employee, NZ([B1]+[B2])/60 AS Break
FROM [Daily Production] INNER JOIN Employee ON [Daily Production].ID =
Employee.ID
WHERE ((([Daily Production].PDate)=[Enter start date]));
then on the actual report I have the following:
Percentage =([totalearnedhr]/([tpaid]-([tproject]+[tstation]+[break])))
TotalEarnedHrs = Sum([earned hrs])
tpaid =([twork]-[Lunch])
tproject =Sum([project])/60
tstation =Sum([station])/60
break =Sum([Break])
lunch =Sum([l])/60
twork =[TOTALHR]/60
Need help!!! PLEASE