Counting specific characters in fields

[afilotimos]

Hi there, i have this problem:
I have a Table with the fields:

ID Student_ID Date h1 h2 h3 h4 h5 h6 h7

the last 7 fields hold one these values: A , B, C, D or empty
example
ID Student_ID Date h1 h2 h3 h4 h5 h6 h7
------------------------------------------------------------------
1 20 1/1/12 A A A A
2 20 3/2/12 A A A A B B B
3 21 3/2/12 C C C A B
...
How can sum (or count) all the "A"s and all the "B"s etc for each Student_ID?????
I also need to be able to filter the Date field.
I tried to make a query , but it doesn't work.

Thank you anyway!!

 

[jdraw]

You should normalize your table.

 

[plog]

jdraw is correct. Once you properly structure your data this becomes simple. Whenever you have field names that are numerated (h1, h2, h3, ...) that data should be in a different table with a field for the number, a field for the actual data and an id field that ties back to the initial table. Once you have that you run an aggregate query grouping by the id and value and counting the records.

Additionally, 'Date' is a reserved word making it a bad name for a field. I'd prefix it with what the date refers to (i.e. GradeDate, StudentDate, etc.). I'd also do the same with the field called ID.

 

[John Big Booty]

Further to the previous suggestions that your data needs to be Normalised this tutorial should help you through the process.

Once the data is normalised it will become a straightforward matter of using the Count function to count the various scores for each student.

 

[afilotimos]

Thank you, I'll try that !!

 

[afilotimos]

jdraw is correct. Once you properly structure your data this becomes simple. Whenever you have field names that are numerated (h1, h2, h3, ...) that data should be in a different table with a field for the number, a field for the actual data and an id field that ties back to the initial table. Once you have that you run an aggregate query grouping by the id and value and counting the records.

Additionally, 'Date' is a reserved word making it a bad name for a field. I'd prefix it with what the date refers to (i.e. GradeDate, StudentDate, etc.). I'd also do the same with the field called ID.

first sorry for the filed names you're right !
so you say i should have 2 tables :
TABLE 1
Absense_ID Student_ID Absense_Date


TABLE 2
Details_ID Absense_ID H1 H2 H3 H4 H5 H6 H7
???

But what's the difference???
Memory or storage is not an issue.
This database stores the absenses of students every day in a highschool
if he miss the class with no justification he is marked with absense type A
if he was ill, type B
if he is justified from parents, type C etc....
When i inform parents for the students absenses i want a form or report to calculate (with a query) the totals of every type of absenses for this student.

 

[tehNellie]

IN your example, there is no difference, you've still not normalised the structure of Table2.

What Does "H" represent?

You basically want for table 2

table2ID
absenseID
definition of H
Value of H

What is H1, H2, H3 etc etc? What do those fields represent?

You might need another table to hold those definitions so you'd have:

Table2ID
AbsenseID
HdefinitionID
value of H

 

[tehNellie]

In your example, there is no difference, you've still not normalised the structure of Table2.

What Does "H" represent?

You basically want for table 2

table2ID
absenseID
definition of H
Value of H

What is H1, H2, H3 etc etc? What do those fields represent?

You might need another table to hold those definitions so you'd have:

Table2ID
AbsenseID
HdefinitionID
value of H

 

[afilotimos]

H1 H2 .... represent Hours of class. (greek educational system).
which means that if the student missed the 3 first hours of class without excuse i add to the record (after student_id, absense_id) :


.... H1 H2 H3 H4 H5 H6 H7
A A A
I need to sumirize all the A's and all the B's and so on for each student but also i need the information about which hours he missed (1st, 2nd ,.... )