Hello everyone, I created a database in which the user selects an option from a radio for various indicators. I'm trying to get a count of the following:

 

total HE for indicator 1.1 for high schools

total EFF for indicator 1.1 for high schools

total NI for indicator 1.1 for high schools

total U for indicator 1.1 for high schools

(continues for all the indicators)

 

Same as above for Middle, Elementary...

 

Then I want to get a total count (Elem+Middle+High) for all HE, EFF, NI, U 

 

What I've done is create a flag field where (ex:)

HE_Indicator1.1_Flag = If(Indicator1.1 = "HE"; 1; "")

 

I've then created separate tables for counts (HS_Count, MS_Count...etc) which are related by the type of school they are and in there are global fields that do the following:

Count(HE_Indicator1.1_flag), so on and so on.

 

Then I have another table for my totals and it's not related to any other table. In there are fields that do the following:

HS_Count::Count_1.1_HE + MS_Count::Count 1.1_HE + ELEM__Count::Count 1.1_HE

 

Now both the counts for the Schools and total general count tables are working fine, but if I modify any of the indicators in the main table to test the numbers.. the numbers won't change. For example, after completing all the tables listed above I would get the following results:

 

High schools

• HE = 2
• EFF = 25
• NI = 1
• U = 0

Middle schools

• HE = 5
• EFF = 33
• NI = 1
• U = 0

Elementary schools

• HE = 6
• EFF = 20
• NI = 4
• U = 0

Total Table (sum of the previous 3 tables; a general count)

• HE = 13
• EFF = 78
• NI = 6
• U = 0

It worked exactly how I wanted but if I modify any of the indicators, for example I make 1 of the HE in High Schools an EFF, that SHOULD give me for High Schools:

• HE = 1
• EFF = 26
• NI = 1
• U = 0

and then the total SHOULD change to:

• HE = 12
• EFF = 79
• NI = 6
• U = 0

I use the word SHOULD right? But it is not changing at all... obviously I'm doing something wrong... any ideas? I've posted a screen shot of what the indicator portion looks like, hopefully the rest of this post is understandable.. sorry english is not my first language.

Some more background would be useful here. On the face of it, i seems to me that if you want to summarize this data without a serious pain in where it really hurts, you will need to make each response an individual record in a related table.