Count weeks of 0 attendance

[rebecky via AccessMonster.com]

Hello. I am working on an attendance subform. The control source is an
attendance table - I wrote a little program that enters the date of the
beginning of the week that the student starts and then enters 180 new records
in the attendance table with a new date for each week for the next 180 weeks
for each record. Hard to explain. Anyway, on this form, I want to be given
a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[KARL DEWEY]

Do you have a record per day per student
OR
record per week per student with fields for each day of the week?

 

[rebecky via AccessMonster.com]

Record per week per student with fields for each day of the week. I was
thinking I could write an expression to total the hours per week and then
have that field checked for 3 zero's in a row.......?

Do you have a record per day per student
OR
record per week per student with fields for each day of the week?

Hello. I am working on an attendance subform. The control source is an
attendance table - I wrote a little program that enters the date of the

[quoted text clipped - 3 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[KARL DEWEY]

Try this in a totals query --
Attendance1: Nz([Mon]) + Nz([Tues]) + Nz([Wed]) + Nz([Thr]) + Nz([Fri])
Replace GROUP BY Sum
Criteria 0

Your_Date_Field
Criteria Between DateAdd(“wwâ€, -3, Date()-Format(Date(),â€wâ€)) And
Date()-Format(Date(),â€wâ€)+2


--
KARL DEWEY
Build a little - Test a little

Record per week per student with fields for each day of the week. I was
thinking I could write an expression to total the hours per week and then
have that field checked for 3 zero's in a row.......?

Do you have a record per day per student
OR
record per week per student with fields for each day of the week?

Hello. I am working on an attendance subform. The control source is an
attendance table - I wrote a little program that enters the date of the

[quoted text clipped - 3 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[rebecky via AccessMonster.com]

I get data type mismatch in query expression

Try this in a totals query --
Attendance1: Nz([Mon]) + Nz([Tues]) + Nz([Wed]) + Nz([Thr]) + Nz([Fri])
Replace GROUP BY Sum
Criteria 0

Your_Date_Field
Criteria Between DateAdd(“wwâ€, -3, Date()-Format(Date(),â€wâ€)) And
Date()-Format(Date(),â€wâ€)+2

Record per week per student with fields for each day of the week. I was
thinking I could write an expression to total the hours per week and then

[quoted text clipped - 9 lines]

 

[KARL DEWEY]

Post your query SQL.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.
--
KARL DEWEY
Build a little - Test a little

I get data type mismatch in query expression

Try this in a totals query --
Attendance1: Nz([Mon]) + Nz([Tues]) + Nz([Wed]) + Nz([Thr]) + Nz([Fri])
Replace GROUP BY Sum
Criteria 0

Your_Date_Field
Criteria Between DateAdd(“wwâ€, -3, Date()-Format(Date(),â€wâ€)) And
Date()-Format(Date(),â€wâ€)+2

Record per week per student with fields for each day of the week. I was
thinking I could write an expression to total the hours per week and then

[quoted text clipped - 9 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[rebecky via AccessMonster.com]

SELECT [Attendance Follow Up].[DATE 1], Sum(Nz([hrs1])+Nz([hrs2])+Nz([hrs3])
+Nz([hrs4])+Nz([hrs5])) AS Attendance1
FROM [Attendance Follow Up]
GROUP BY [Attendance Follow Up].[DATE 1]
HAVING ((([Attendance Follow Up].[DATE 1]) Between DateAdd("“wwâ€",-3,Date()-
Format(Date(),"â€wâ€")) And Date()-Format(Date(),"â€wâ€")+2) AND ((Sum(Nz([hrs1])
+Nz([hrs2])+Nz([hrs3])+Nz([hrs4])+Nz([hrs5])))=0));

Post your query SQL.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

I get data type mismatch in query expression

[quoted text clipped - 12 lines]

 

[KARL DEWEY]

Is [Attendance Follow Up].[DATE 1] a DateTime datatype or text field?

Try running the query without the HAVING part of the SQL.
--
KARL DEWEY
Build a little - Test a little

SELECT [Attendance Follow Up].[DATE 1], Sum(Nz([hrs1])+Nz([hrs2])+Nz([hrs3])
+Nz([hrs4])+Nz([hrs5])) AS Attendance1
FROM [Attendance Follow Up]
GROUP BY [Attendance Follow Up].[DATE 1]
HAVING ((([Attendance Follow Up].[DATE 1]) Between DateAdd("“wwâ€",-3,Date()-
Format(Date(),"â€wâ€")) And Date()-Format(Date(),"â€wâ€")+2) AND ((Sum(Nz([hrs1])
+Nz([hrs2])+Nz([hrs3])+Nz([hrs4])+Nz([hrs5])))=0));

Post your query SQL.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.

I get data type mismatch in query expression

[quoted text clipped - 12 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[rebecky via AccessMonster.com]

Yes. Date/Time field. Thank you for helping me with this...but I do not know
what you mean by the "having" part of the SQL?

Is [Attendance Follow Up].[DATE 1] a DateTime datatype or text field?

Try running the query without the HAVING part of the SQL.

SELECT [Attendance Follow Up].[DATE 1], Sum(Nz([hrs1])+Nz([hrs2])+Nz([hrs3])
+Nz([hrs4])+Nz([hrs5])) AS Attendance1

[quoted text clipped - 12 lines]

 

[rebecky via AccessMonster.com]

oops...I see the "having" part. Will try it.

Is [Attendance Follow Up].[DATE 1] a DateTime datatype or text field?

Try running the query without the HAVING part of the SQL.

SELECT [Attendance Follow Up].[DATE 1], Sum(Nz([hrs1])+Nz([hrs2])+Nz([hrs3])
+Nz([hrs4])+Nz([hrs5])) AS Attendance1

[quoted text clipped - 12 lines]

 

[rebecky via AccessMonster.com]

Returns all records with zero.

Is [Attendance Follow Up].[DATE 1] a DateTime datatype or text field?

Try running the query without the HAVING part of the SQL.

SELECT [Attendance Follow Up].[DATE 1], Sum(Nz([hrs1])+Nz([hrs2])+Nz([hrs3])
+Nz([hrs4])+Nz([hrs5])) AS Attendance1

[quoted text clipped - 12 lines]

 

[KARL DEWEY]

It should return all records with a weekly sum, not just those that equal
zero for the week for a three week period.

--
KARL DEWEY
Build a little - Test a little

Returns all records with zero.

Is [Attendance Follow Up].[DATE 1] a DateTime datatype or text field?

Try running the query without the HAVING part of the SQL.

SELECT [Attendance Follow Up].[DATE 1], Sum(Nz([hrs1])+Nz([hrs2])+Nz([hrs3])
+Nz([hrs4])+Nz([hrs5])) AS Attendance1

[quoted text clipped - 12 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[rebecky via AccessMonster.com]

Yes it does if I take out the zero criteria in the query grid. But there is
no 3 week period distinction without the "having" part.........

It should return all records with a weekly sum, not just those that equal
zero for the week for a three week period.

Returns all records with zero.

[quoted text clipped - 6 lines]

 

[KARL DEWEY]

I think I see a problem in that the coping and pasting process added some
quotes. Edit to remove two sets of quotes that are in the HAVING part -
DateAdd("“wwâ€",- to look like DateAdd("ww",- and throughout.

HAVING ((([Attendance Follow Up].[DATE 1]) Between DateAdd("ww",-3,Date()-
Format(Date(),"w")) And Date()-Format(Date(),"w")+2) AND ((Sum(Nz([hrs1])
+Nz([hrs2])+Nz([hrs3])+Nz([hrs4])+Nz([hrs5])))=0));


--
KARL DEWEY
Build a little - Test a little

Yes it does if I take out the zero criteria in the query grid. But there is
no 3 week period distinction without the "having" part.........

It should return all records with a weekly sum, not just those that equal
zero for the week for a three week period.

Returns all records with zero.

[quoted text clipped - 6 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?

 

[rebecky via AccessMonster.com]

I think this will get it but it is showing the past 4 weeks instead of three.
We take a specific action for 3 weeks of no attendance. How should I edit
that? And THANKS!!!!

I think I see a problem in that the coping and pasting process added some
quotes. Edit to remove two sets of quotes that are in the HAVING part -
DateAdd("“wwâ€",- to look like DateAdd("ww",- and throughout.

HAVING ((([Attendance Follow Up].[DATE 1]) Between DateAdd("ww",-3,Date()-
Format(Date(),"w")) And Date()-Format(Date(),"w")+2) AND ((Sum(Nz([hrs1])
+Nz([hrs2])+Nz([hrs3])+Nz([hrs4])+Nz([hrs5])))=0));

Yes it does if I take out the zero criteria in the query grid. But there is
no 3 week period distinction without the "having" part.........

[quoted text clipped - 7 lines]

 

[KARL DEWEY]

Try changing HAVING ((([Attendance Follow Up].[DATE 1]) Between
DateAdd("ww",-3,Date()- to
HAVING ((([Attendance Follow Up].[DATE 1]) Between DateAdd("ww",-2,Date()-

-3 to -2
--
KARL DEWEY
Build a little - Test a little

I think this will get it but it is showing the past 4 weeks instead of three.
We take a specific action for 3 weeks of no attendance. How should I edit
that? And THANKS!!!!

I think I see a problem in that the coping and pasting process added some
quotes. Edit to remove two sets of quotes that are in the HAVING part -
DateAdd("“wwâ€",- to look like DateAdd("ww",- and throughout.

HAVING ((([Attendance Follow Up].[DATE 1]) Between DateAdd("ww",-3,Date()-
Format(Date(),"w")) And Date()-Format(Date(),"w")+2) AND ((Sum(Nz([hrs1])
+Nz([hrs2])+Nz([hrs3])+Nz([hrs4])+Nz([hrs5])))=0));

Yes it does if I take out the zero criteria in the query grid. But there is
no 3 week period distinction without the "having" part.........

[quoted text clipped - 7 lines]

a flag when a student has "0" attendance for 3 weeks in a row. Do you know
how I can do this?