aggregate function across fields?

  • Thread starter Thread starter Tim Thorp
  • Start date Start date
T

Tim Thorp

I have a scores database where for a given student, they take a number of
tests and their scores for the series of tests are all based on the same
categories. but they are tested across several disciplines. A good example
is that I am testing agility, strength and coordination across tennis,
basketball and volleyball

I designed the database with a field for the sport and one field for
agility, one for strength and one for coordination.

I want to calculate averages and am stuck. It is more easy for me to
calculate the average for agility, but not for tennis.

Is it possible for me to Avg([agility] AND [strength] AND [coordination]) ??

Tim Thorp
 
This isn't very normalized. Could you please type in about 10 sample records
as well as the expected results/display?
 
Thanks for writing!!

10 records where Station ID is what is being tested follow here and a good
example of the output is for examID 1341, I want an expression that will
yield the average of the values stored in Moral1, Moral2, Moral3, Moral4,
Moral5 :
ExamID StudentInitials StudentID PatientID StationID ExamDate
DateEntered StudentSchool Communication History Physical Moral1 Moral2
Moral3 Moral4 Moral5
1344 GB 00001 000000017 5 8 /14/2003 15-Dec-03 2 6 6 6
1343 GB 00001 000000037 4 8 /14/2003 15-Dec-03 2 4 2
1342 GB 00001 000000036 3 8 /14/2003 15-Dec-03 2 6 6 6
1341 GB 00001 000000011 1 8 /14/2003 15-Dec-03 2 6 5 5 4 4 5 4 5
1340 GB 00001 000000040 6 8 /14/2003 15-Dec-03 2 6 5 3
1299 GB 00001 000000060 2 8 /14/2003 15-Dec-03 2 3 3 3 2 3 3 3
1345 GB 00001 000000014 7 8 /14/2003 15-Dec-03 2 5 3 5
1312 SB 00002 000000036 3 8 /14/2003 15-Dec-03 2 5 5 5
1298 SB 00002 000000060 2 8 /14/2003 15-Dec-03 2 6 6 4 5 4 4 4
1311 SB 00002 000000011 1 8 /14/2003 15-Dec-03 2 5 4 5 3 3 4 4 3
1313 SB 00002 000000037 4 8 /14/2003 15-Dec-03 2 4 2
1314 SB 00002 000000017 5 8 /14/2003 15-Dec-03 2 6 6 5
1315 SB 00002 000000014 7 8 /14/2003 15-Dec-03 2 5 5 5
1310 SB 00002 000000040 6 8 /14/2003 15-Dec-03 2 5 4 5
1297 ZS 00003 000000060 2 8 /14/2003 15-Dec-03 2 5 4 3 3 3 3 4
1309 ZS 00003 000000014 7 8 /14/2003 15-Dec-03 2 6 5 4
1308 ZS 00003 000000017 5 8 /14/2003 15-Dec-03 2 6 5 5
1307 ZS 00003 000000037 4 8 /14/2003 15-Dec-03 2 5 5
1306 ZS 00003 000000036 3 8 /14/2003 15-Dec-03 2 5 5 5
1305 ZS 00003 000000011 1 8 /14/2003 15-Dec-03 2 5 5 5 4 4 4 4 5
1304 ZS 00003 000000040 6 8 /14/2003 15-Dec-03 2 3 5 5

Duane Hookom said:
This isn't very normalized. Could you please type in about 10 sample records
as well as the expected results/display?

--
Duane Hookom
MS Access MVP


Tim Thorp said:
I have a scores database where for a given student, they take a number of
tests and their scores for the series of tests are all based on the same
categories. but they are tested across several disciplines. A good example
is that I am testing agility, strength and coordination across tennis,
basketball and volleyball

I designed the database with a field for the sport and one field for
agility, one for strength and one for coordination.

I want to calculate averages and am stuck. It is more easy for me to
calculate the average for agility, but not for tennis.

Is it possible for me to Avg([agility] AND [strength] AND
Click to expand...
[coordination])
??

Tim Thorp
Click to expand...
Click to expand...
 
The average of the Moralx fields I believe should be:
=IIf( (5 + IsNull(Moral1) + + IsNull(Moral2) + IsNull(Moral3) +
IsNull(Moral4) + IsNull(Moral5) )=0, 0, ( Nz(Moral1,0) + Nz(Moral2,0) +
Nz(Moral3,0) + Nz(Moral4,0) + Nz(Moral5,0) ) / (5 + IsNull(Moral1) + +
IsNull(Moral2) + IsNull(Moral3) + IsNull(Moral4) + IsNull(Moral5) ) )

If your data was normalized this would not be such a horrible expression.


--
Duane Hookom
MS Access MVP
--

Tim Thorp said:
Thanks for writing!!

10 records where Station ID is what is being tested follow here and a good
example of the output is for examID 1341, I want an expression that will
yield the average of the values stored in Moral1, Moral2, Moral3, Moral4,
Moral5 :
ExamID StudentInitials StudentID PatientID StationID ExamDate
DateEntered StudentSchool Communication History Physical Moral1 Moral2
Moral3 Moral4 Moral5
1344 GB 00001 000000017 5 8 /14/2003 15-Dec-03 2 6 6 6
1343 GB 00001 000000037 4 8 /14/2003 15-Dec-03 2 4 2
1342 GB 00001 000000036 3 8 /14/2003 15-Dec-03 2 6 6 6
1341 GB 00001 000000011 1 8 /14/2003 15-Dec-03 2 6 5 5 4 4 5 4 5
1340 GB 00001 000000040 6 8 /14/2003 15-Dec-03 2 6 5 3
1299 GB 00001 000000060 2 8 /14/2003 15-Dec-03 2 3 3 3 2 3 3 3
1345 GB 00001 000000014 7 8 /14/2003 15-Dec-03 2 5 3 5
1312 SB 00002 000000036 3 8 /14/2003 15-Dec-03 2 5 5 5
1298 SB 00002 000000060 2 8 /14/2003 15-Dec-03 2 6 6 4 5 4 4 4
1311 SB 00002 000000011 1 8 /14/2003 15-Dec-03 2 5 4 5 3 3 4 4 3
1313 SB 00002 000000037 4 8 /14/2003 15-Dec-03 2 4 2
1314 SB 00002 000000017 5 8 /14/2003 15-Dec-03 2 6 6 5
1315 SB 00002 000000014 7 8 /14/2003 15-Dec-03 2 5 5 5
1310 SB 00002 000000040 6 8 /14/2003 15-Dec-03 2 5 4 5
1297 ZS 00003 000000060 2 8 /14/2003 15-Dec-03 2 5 4 3 3 3 3 4
1309 ZS 00003 000000014 7 8 /14/2003 15-Dec-03 2 6 5 4
1308 ZS 00003 000000017 5 8 /14/2003 15-Dec-03 2 6 5 5
1307 ZS 00003 000000037 4 8 /14/2003 15-Dec-03 2 5 5
1306 ZS 00003 000000036 3 8 /14/2003 15-Dec-03 2 5 5 5
1305 ZS 00003 000000011 1 8 /14/2003 15-Dec-03 2 5 5 5 4 4 4 4 5
1304 ZS 00003 000000040 6 8 /14/2003 15-Dec-03 2 3 5 5

Duane Hookom said:
This isn't very normalized. Could you please type in about 10 sample records
as well as the expected results/display?

--
Duane Hookom
MS Access MVP


Tim Thorp said:
I have a scores database where for a given student, they take a number of
tests and their scores for the series of tests are all based on the same
categories. but they are tested across several disciplines. A good example
is that I am testing agility, strength and coordination across tennis,
basketball and volleyball

I designed the database with a field for the sport and one field for
agility, one for strength and one for coordination.

I want to calculate averages and am stuck. It is more easy for me to
calculate the average for agility, but not for tennis.

Is it possible for me to Avg([agility] AND [strength] AND
Click to expand...
[coordination])
??

Tim Thorp
Click to expand...
Click to expand...
Click to expand...
 
Back
Top