sum up user occurences in a query

[jacko999]

in my database i have ten fields each containing user initials as a textfield. we need for each user we need to know how many times each user appears in each field and displayed in a number.

Chris

Chris

 

[SkipVought]

hi,

horrific table!!!

Select F1, Count(f1) From [TheTable] Group by F1
Union
Select F2, Count(f2) From [TheTable] Group by F2
Union
...
Select F10, Count(f10) From [TheTable] Group by F10

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[dhookom]

In case you are wondering about Skip's "horrific table!!!" comment, you might want to find some good resources on normalization and table structures.

http://www.datamodel.org/NormalizationRules.html

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]