Problem in Cross tab query

Anand Vaidya · Aug 30, 2006

Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A") AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the expression value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs , then 0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the record is not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something like this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00 AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Duane Hookom · Aug 30, 2006

Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

Anand Vaidya · Aug 31, 2006

Duane Hookom , I am using the count for checking if the record is present or
not for that perticular day. 1 if present and 0 if absent.
The nested iif conditions should check whether Net_Hrs_Worked is
< 4 hrs or

4hr and <6.30 hrs or
6:30 hrs

and give 0, 0.5 or 1 accordingly only if the
Count([tblDaily_Attendance].PK_DailyAttendance_Id]) is 1

-----------
Anand Vaidya
I am here to know.

Duane Hookom said:
Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

--
Duane Hookom
MS Access MVP

Anand Vaidya said:

Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A")
AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN
tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the expression value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs , then
0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the record is not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something like this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00 AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Click to expand...

Duane Hookom · Aug 31, 2006

What happened when you tried my suggestion? Did it work or not? What is the
full SQL after you tried my suggestion?

--
Duane Hookom
MS Access MVP

Anand Vaidya said:
Duane Hookom , I am using the count for checking if the record is present
or
not for that perticular day. 1 if present and 0 if absent.
The nested iif conditions should check whether Net_Hrs_Worked is
< 4 hrs or

4hr and <6.30 hrs or
6:30 hrs

Click to expand...

and give 0, 0.5 or 1 accordingly only if the
Count([tblDaily_Attendance].PK_DailyAttendance_Id]) is 1

-----------
Anand Vaidya
I am here to know.

Duane Hookom said:

Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

--
Duane Hookom
MS Access MVP

Anand Vaidya said:

Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A")
AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN
tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the expression
value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs , then
0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the record is
not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something like
this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00
AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Click to expand...

Click to expand...

Anand Vaidya · Sep 4, 2006

Duane , I am getting the same results for both the cross tabs -
previous one--
CountOfPK_DailyAttendance_Id:
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,1,0)

the one which you suggested--
val:
IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

Both the above conditions are giving the same result.
I changed an emp's record giving Net_Hrs_Worked as 3:59 Hrs and 4:44 Hrs
for two different attendance days but instead of displaying 0 and 0.5 for
these records , its displaying 1 and 1 .

-----------
Anand Vaidya
I am here to know.

Duane Hookom said:
What happened when you tried my suggestion? Did it work or not? What is the
full SQL after you tried my suggestion?

--
Duane Hookom
MS Access MVP

Anand Vaidya said:

Duane Hookom , I am using the count for checking if the record is present
or
not for that perticular day. 1 if present and 0 if absent.
The nested iif conditions should check whether Net_Hrs_Worked is
< 4 hrs or

4hr and <6.30 hrs or
6:30 hrs

Click to expand...

and give 0, 0.5 or 1 accordingly only if the
Count([tblDaily_Attendance].PK_DailyAttendance_Id]) is 1

-----------
Anand Vaidya
I am here to know.

Duane Hookom said:

Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

--
Duane Hookom
MS Access MVP

Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A")
AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN
tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the expression
value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs , then
0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the record is
not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something like
this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00
AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Click to expand...

Click to expand...

Duane Hookom · Sep 4, 2006

Ok, I think the best solution is to create a table of ranges

MinTime MaxTime TheValue
0:00 #4:00:00# 0.0
#4:00:00# #6:30:00# 0.5
#6:30:00# 999 1.0

You can then add this table to your query and set the criteria un
Net_Hrs_Worked to

=MinTime And <MaxTime

Add the [TheValue] field to the grid so that you can count or sum it.

When the time ranges change, just change the values in the table.

--
Duane Hookom
MS Access MVP

Anand Vaidya said:
Duane , I am getting the same results for both the cross tabs -
previous one--
CountOfPK_DailyAttendance_Id:
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,1,0)

the one which you suggested--
val:
IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

Both the above conditions are giving the same result.
I changed an emp's record giving Net_Hrs_Worked as 3:59 Hrs and 4:44 Hrs
for two different attendance days but instead of displaying 0 and 0.5 for
these records , its displaying 1 and 1 .

-----------
Anand Vaidya
I am here to know.

Duane Hookom said:

What happened when you tried my suggestion? Did it work or not? What is
the
full SQL after you tried my suggestion?

--
Duane Hookom
MS Access MVP

Anand Vaidya said:

Duane Hookom , I am using the count for checking if the record is
present
or
not for that perticular day. 1 if present and 0 if absent.
The nested iif conditions should check whether Net_Hrs_Worked is
< 4 hrs or
4hr and <6.30 hrs or
6:30 hrs
and give 0, 0.5 or 1 accordingly only if the
Count([tblDaily_Attendance].PK_DailyAttendance_Id]) is 1

-----------
Anand Vaidya
I am here to know.

:

Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

--
Duane Hookom
MS Access MVP

message
Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A")
AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN
tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the expression
value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs ,
then
0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the record
is
not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something like
this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00
AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific
expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Click to expand...

Click to expand...

Anand Vaidya · Sep 8, 2006

hi Duane, your suggestion is working absolutely fine to display TheValue as
0, 0.5 or 1 for employee's "present" days . But how do I add "0" or "-" to a
date on which the employee is not present because his record would not be
existing for that date.How do I club this condition with the one you have
suggested?
-----------
Anand Vaidya
I am here to know.

Duane Hookom said:
Ok, I think the best solution is to create a table of ranges

MinTime MaxTime TheValue
0:00 #4:00:00# 0.0
#4:00:00# #6:30:00# 0.5
#6:30:00# 999 1.0

You can then add this table to your query and set the criteria un
Net_Hrs_Worked to

=MinTime And <MaxTime

Click to expand...

Add the [TheValue] field to the grid so that you can count or sum it.

When the time ranges change, just change the values in the table.

--
Duane Hookom
MS Access MVP

Anand Vaidya said:

Duane , I am getting the same results for both the cross tabs -
previous one--
CountOfPK_DailyAttendance_Id:
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,1,0)

the one which you suggested--
val:
IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

Both the above conditions are giving the same result.
I changed an emp's record giving Net_Hrs_Worked as 3:59 Hrs and 4:44 Hrs
for two different attendance days but instead of displaying 0 and 0.5 for
these records , its displaying 1 and 1 .

-----------
Anand Vaidya
I am here to know.

Duane Hookom said:

What happened when you tried my suggestion? Did it work or not? What is
the
full SQL after you tried my suggestion?

--
Duane Hookom
MS Access MVP

Duane Hookom , I am using the count for checking if the record is
present
or
not for that perticular day. 1 if present and 0 if absent.
The nested iif conditions should check whether Net_Hrs_Worked is
< 4 hrs or
4hr and <6.30 hrs or
6:30 hrs
and give 0, 0.5 or 1 accordingly only if the
Count([tblDaily_Attendance].PK_DailyAttendance_Id]) is 1

-----------
Anand Vaidya
I am here to know.

:

Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

--
Duane Hookom
MS Access MVP

message
Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A")
AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN
tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the expression
value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs ,
then
0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the record
is
not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something like
this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00
AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific
expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Click to expand...

Click to expand...

Duane Hookom · Sep 8, 2006

Have you created a crosstab that encorporates the table of ranges? If so,
what is the sql view? You can generally use Nz() in the "Value" of the
crosstab to display any value you want.

--
Duane Hookom
MS Access MVP

Anand Vaidya said:
hi Duane, your suggestion is working absolutely fine to display TheValue
as
0, 0.5 or 1 for employee's "present" days . But how do I add "0" or "-" to
a
date on which the employee is not present because his record would not be
existing for that date.How do I club this condition with the one you have
suggested?
-----------
Anand Vaidya
I am here to know.

Duane Hookom said:

Ok, I think the best solution is to create a table of ranges

MinTime MaxTime TheValue
0:00 #4:00:00# 0.0
#4:00:00# #6:30:00# 0.5
#6:30:00# 999 1.0

You can then add this table to your query and set the criteria un
Net_Hrs_Worked to

=MinTime And <MaxTime

Click to expand...

Add the [TheValue] field to the grid so that you can count or sum it.

When the time ranges change, just change the values in the table.

--
Duane Hookom
MS Access MVP

Anand Vaidya said:

Duane , I am getting the same results for both the cross tabs -
previous one--
CountOfPK_DailyAttendance_Id:
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,1,0)

the one which you suggested--
val:
IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

Both the above conditions are giving the same result.
I changed an emp's record giving Net_Hrs_Worked as 3:59 Hrs and 4:44
Hrs
for two different attendance days but instead of displaying 0 and 0.5
for
these records , its displaying 1 and 1 .

-----------
Anand Vaidya
I am here to know.

:

What happened when you tried my suggestion? Did it work or not? What
is
the
full SQL after you tried my suggestion?

--
Duane Hookom
MS Access MVP

message
Duane Hookom , I am using the count for checking if the record is
present
or
not for that perticular day. 1 if present and 0 if absent.
The nested iif conditions should check whether Net_Hrs_Worked is
< 4 hrs or
4hr and <6.30 hrs or
6:30 hrs
and give 0, 0.5 or 1 accordingly only if the
Count([tblDaily_Attendance].PK_DailyAttendance_Id]) is 1

-----------
Anand Vaidya
I am here to know.

:

Try add Count()s in the 2nd and 3rd IIf()s.

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])>#6:30:00
AM#,1,IIf(Count([tblDaily_Attendance].[Net_Hrs_Worked])<#4:00:00
AM#,0,0.5)),0)

--
Duane Hookom
MS Access MVP

message
Hello all,
I created a cross tab query, which is running fine, as -

TRANSFORM
IIf(Count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,"P","A")
AS
CountOfPK_DailyAttendance_Id
SELECT tblEmployees.Name, tblDepartments.Dept_Name
FROM tblDepartments INNER JOIN (tblEmployees INNER JOIN
tblDaily_Attendance
ON tblEmployees.PK_Emp_Id = tblDaily_Attendance.FK_Emp_Id) ON
tblDepartments.PK_Dept_Id = tblEmployees.FK_Dept_Id
WHERE (((tblDaily_Attendance.Attendance_Date) Between
Format([forms].[frmReports2].[txtFromDateAtt],"m/d/yyyy") And
Format([forms].[frmReports2].[txtToDateAtt],"m/d/yyyy")))
GROUP BY tblEmployees.Name, tblDepartments.Dept_Name
PIVOT Day([Attendance_Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Now I want to change the transform line such that
if tblDaily_Attendance.Net_Hrs_Worked > 6.30 hrs then the
expression
value
should be 1
if tblDaily_Attendance.Net_Hrs_Worked > 4.00 hrs and < 6.30 hrs
,
then
0.5
if tblDaily_Attendance.Net_Hrs_Worked < 4.00 hrs or if the
record
is
not
present for that day (ie record count=0), then 0
so I created a query where the transform part looks something
like
this -

IIf(Count([tblDaily_Attendance].[PK_DailyAttendance_Id])>0,IIf([tblDaily_Attendance].[Net_Hrs_Worked]>#6:30:00
AM#,1,IIf([tblDaily_Attendance].[Net_Hrs_Worked]<#4:00:00
AM#,0,0.5)),0)

but doesn't work and pops up an error -

"You tried to execute a query that does not include specific
expression
'iif(count(tblDaily_Attendance.PK_DailyAttendance_Id)>0,iif(tblDaily_Attendance.Net_Hrs_Worked>#12/30/1899
6:30.0#,1,iif(tblDaily_Attendance.Net_Hrs_Worked<#12/30/1899
4:0:0#,0,0.5)),0)' as part of an aggregate function.

Click to expand...

Click to expand...