Counting Distinct Valve in a row

[weilerdo]

Hi All, I am stuck, and can't get my head around an easy way to do this. I have a table that has an Team_mem,strmonth, and 31 other fields 01 through 31 with valves of D, V, A etc and I need a way to count how many times the letter V shows up in fields 01 through 31 in a given row. Example

Team_mem strmonth, 01 02 03 04 05 06 07 08 09 etc.....
Bill P May 2007 D D D A V V D D
Joe E May 2007 D D D V V V V D D

I need to be able to see a result like
Team_mem strmonth, Count of V Count of A
Bill P May 2007 2 1
Joe E May 2007 4

Thank you for all your assitance in advance

 

[Dennisk]

your table is not normalised correctly. Your repeating columns (days) should be in a seperate table with its own Primary key and a foreign key wich is the ID of your team Member. Otherwise you will require either 31 queries and a union query od a domain agregate to count the number of occurences V for each column.