Counting scores

[palacefan]

I produce a query that looks like this -

FieldA FieldB FieldC FieldD FieldE FieldF FieldG FieldH
4 5 4 3 4 5 3 4
4 4 4 4 4 4 4 5
5 5 5 5 4 4 4 3
3 4 4 4 3 4 5 4

How can I produce a table which finds out how many 1s, 2s, 3s, 4s & 5s there are in each column?

Result would look like this -
Number FieldA FieldB FieldC FieldD FieldE FieldF FieldG FieldH
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
3 1 0 0 1 1 0 1 1
4 2 2 3 2 3 3 2 2
5 1 2 1 1 0 1 1 1

[PEB]

Hi,

You can do it...

All You need is a temporary table with one column - mynumber where to store your numbers from 1 to 5 i.e

and the function dcount

Invoke a new query based on the temporary table!

For exemple

FieldA in your table will be:

Dcount("FieldA","YourQuery","FieldA="+Str(mynumber ))

And so on...

:)

Best regards!

[palacefan]

Hi,

You can do it...

All You need is a temporary table with one column - mynumber where to store your numbers from 1 to 5 i.e

and the function dcount

Invoke a new query based on the temporary table!

For exemple

FieldA in your table will be:

Dcount("FieldA","YourQuery","FieldA="+Str(mynumber ))

And so on...

:)

Best regards!

Thanks for your help - I'm almost there but now have some very clumsy code which I'm sure someone can help me turn into a do loop. I haven't done much with VBA and my attempts have failed so far.

My code is as follows and uses T-input for now rather than a query as the real thing will and my counts are stored in T-stats

' Count how many values in T-input= 1 and store in T-stats row 1
mySQL = "UPDATE [T-stats]"
mySQL = mySQL + " SET [T-stats]!FieldAcount = (DCount('([T-input].FieldA)', '[T-input]', '[T-input]!FieldA = 1')) "
mySQL = mySQL + " WHERE (([T-stats]![Score] = 1)) "
DoCmd.RunSQL mySQL
mySQL = "UPDATE [T-stats]"
mySQL = mySQL + " SET [T-stats]!FieldBcount = (DCount('([T-input].FieldB)', '[T-input]', '[T-input]!FieldB = 1')) "
mySQL = mySQL + " WHERE (([T-stats]![Score] = 1)) "
DoCmd.RunSQL mySQL
mySQL = "UPDATE [T-stats]"
mySQL = mySQL + " SET [T-stats]!FieldCcount = (DCount('([T-input].FieldC)', '[T-input]', '[T-input]!FieldC = 1')) "
mySQL = mySQL + " WHERE (([T-stats]![Score] = 1)) "
DoCmd.RunSQL mySQL
mySQL = "UPDATE [T-stats]"
mySQL = mySQL + " SET [T-stats]!FieldDcount = (DCount('([T-input].FieldD)', '[T-input]', '[T-input]!FieldD = 1')) "
mySQL = mySQL + " WHERE (([T-stats]![Score] = 1)) "
DoCmd.RunSQL mySQL
mySQL = "UPDATE [T-stats]"
mySQL = mySQL + " SET [T-stats]!FieldEcount = (DCount('([T-input].FieldE)', '[T-input]', '[T-input]!FieldE = 1')) "
mySQL = mySQL + " WHERE (([T-stats]![Score] = 1)) "
DoCmd.RunSQL mySQL

How do I replace everywhere that uses 1 with "mynumber" so that I can loop through with mynumber increasing from 1 to 5 instead of writing the above out 5 times over?

Kind regards

palacefan

[palacefan]

Thanks for your extra instructions - it now makes sense and works brilliantly. Your help was much appreciated.

palacefan