Hi all (again)!!



I posted a question earlier in the list, for which ken gave me a 

solution!! YAY..thanks.



I am trying to count records in a database.  In one table i have a 

student_id and four checkboxes representing attendance at a particular 

event.  so...



Attent table



Student_ID  Month  Week_1   Week_2   Week_3   Week_4   Week_5



5678545      Apr     [x]       []       [x]      []       []

5678545      Apr     [x]       [x]      []       [x]      []

5678545      Apr     []        [x]      []       []       []



* the [] denotes a true/false field



I want to count how many students attended in each week.  I can do this 

for say week_1:



SELECT Count(Attend.Week_1) AS CountWeek1, Tute_Attend.Month 

FROM Attend

GROUP BY Attend.Month, Attend.Week_1

HAVING (((Tute_Attend.Week_1)=True));      '// only want to count fields

                                           '// that are true 



This works well, giving me a table with the count of week_1.



However i want to produce a count of Week_2, through to week_5.  How can i 

do this in sql.  I have tried to use the where or having clause as above, 

but it won't work. 



Thanks for your help

Hi all (again)!!



I posted a question earlier in the list, for which ken gave me a 

solution!! YAY..thanks.



I have one more problem though with this! :(



I am trying to count records in a database.  In one table i have a 

student_id and four checkboxes representing attendance at a particular 

event.  so...



Attent table



Student_ID  Month  Week_1   Week_2   Week_3   Week_4   Week_5



5678545      Apr     [x]       []       [x]      []       []

5678545      Apr     [x]       [x]      []       [x]      []

5678545      Apr     []        [x]      []       []       []



* the [] denotes a true/false field



I want to count how many students attended in each week.  I can do this 

for say week_1:



SELECT Count(Attend.Week_1) AS CountWeek1, Tute_Attend.Month 

FROM Attend

GROUP BY Attend.Month, Attend.Week_1

HAVING (((Tute_Attend.Week_1)=True));      '// only want to count fields

                                           '// that are true 



This works well, giving me a table with the count of week_1.



However i want to produce a count of Week_2, through to week_5.  How can i 

do this in sql.  I have tried to use the where or having clause as above, 

but it won't work. 



Thanks for your help



Andrew.

You'll probably have to write separate queries for each week. This is

because your table isn't designed optimally.



What you should have is something like:



TABLE:Attendance

Field: StudentID (Number: Long Integer) - references Student.StudentID

Field: WeekID (Number: Long Integer) - references Weeks.WeekID



(and in the table Weeks you could store months and week numbers).



Then your query becomes:



SELECT WeekID, Count(*)

FROM Attendance

GROUP BY WeekID



If you wanted to see particular weeks for a given month it would become:



SELECT a.WeekName, b.Count(*)

FROM Attendance b

INNER JOIN Weeks a

ON b.WeekID = b.WeekID

WHERE a.MonthNumber = 4

GROUP BY a.WeekName



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Andrew Matheson" <amatheso@y...>

Subject: [access_asp] Counting Multiple entries in records.





: Hi all (again)!!

:

: I posted a question earlier in the list, for which ken gave me a

: solution!! YAY..thanks.

:

: I have one more problem though with this! :(

:

: I am trying to count records in a database.  In one table i have a

: student_id and four checkboxes representing attendance at a particular

: event.  so...

:

: Attent table

:

: Student_ID  Month  Week_1   Week_2   Week_3   Week_4   Week_5

:

: 5678545      Apr     [x]       []       [x]      []       []

: 5678545      Apr     [x]       [x]      []       [x]      []

: 5678545      Apr     []        [x]      []       []       []

:

: * the [] denotes a true/false field

:

: I want to count how many students attended in each week.  I can do this

: for say week_1:

:

: SELECT Count(Attend.Week_1) AS CountWeek1, Tute_Attend.Month

: FROM Attend

: GROUP BY Attend.Month, Attend.Week_1

: HAVING (((Tute_Attend.Week_1)=True));      '// only want to count fields

:                                            '// that are true

:

: This works well, giving me a table with the count of week_1.

:

: However i want to produce a count of Week_2, through to week_5.  How can i

: do this in sql.  I have tried to use the where or having clause as above,

: but it won't work.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~