Count # of names in one cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey everyone,

Is it possible to count the number of times something appears in one sigle
cell for each record?

Example: I have a RACE field which was imported from another source.
Sometimes, a person may match two or more races. So here's how the field may
look:

RACE FIELD:
Hispanic
Asian, Caucasian
Afr-American, Hispanic, Asian
Caucasian


You see, sometimes just one race is entered, and other times it's multiple
races. What I need to do is count the number of times a race has been
selected for a patient. Using the same sequence above, I want my newly
calculated field to display:

1
2
3
1

(the number races that were selected). Can I do this?

And yes - I know normalization would be highly recommended. But this is how
the data has been given to me, and all the client wants is a physical count.

Thanks!
 
Here is a quick and easy way to do that.

Public Function RaceCount(strRaces As String) As Integer
dim varRainbow As Variant

varRainBow = Split(strRaces, ",")
RaceCount = Ubound(varRainnow) + 1
End Function

This assumes that as in your example, each race is separated by a comma.
 
1+UBound(SPLIT(fieldName, ","))


as:

? 1+UBound(SPLIT("Asian, Caucasian", ",")), 1+UBound(Split("Afr-American,
Hispanic, Asian", ","))
2 3




but you should consider to normalize your data, the actual representation
does not allow as much "EASY" work as a normalized solution does.





Vanderghast, Access MVP
 
Personally, I would create a table Races with the values that are entered in
the field.
Races
Race (with values Hispanic, Caucasian, Asian, and Afr-American

Then a query using the table in a non-equi join could give you a
"mormalized" list of races

And you could use aggregate queries like the following

SELECT PersonID, Count(Races.Race) as MultiRace
FROM PersonTable INNER JOIN Races
ON PersonTable.Race LIKE "*" & Races.Race & "*"
GROUP BY PersonID

Next question, what are the counts by race
SELECT Races.Race, Count(Races.Race)
FROM PersonTable INNER JOIN Races
ON PersonTable.Race LIKE "*" & Races.Race & "*"
Group by Races.Race

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top