Validation Check Across Multiple fields 1

[pearce64]

name WHO0: WHO1: WHO2: WHO3: WHO4: check
fred 1 1 0
joe 1 1
mike 1 1
steve 1 1
sarah 1 1
pete 1 1
ash 1 1

I have a table (matrix) which records performance scores for individuals.

Each Individual can only have a score (True 1) in any of the WHO fields. I want to populate a further field (check) as part of a validation exercise.

The [check] field will contain 0 if a person has a double entry or 1 if a person only has 1 score. etc. If the person has no score in any of the [WHO] fields then put a dash -

This data has been created by another user, therefore I will add a validation rule to the table once the data has been cleaned.

thanks for the help in advance

 

[oticonaus]

SELECT Table1.Name, Table1.WHO0, Table1.WHO1, Table1.WHO2, Table1.WHO3, Table1.WHO4, nz([WHO0],0)+nz([WHO1],0)+nz([WHO2],0)+nz([WHO3],0)+nz([WHO4],0) AS WHOT, Switch([WHOT]=0,"-",[WHOT]=1,1,[WHOT]>1,0) AS [Check]
FROM Table1;

You can test this query on your data, and if it works, make it into an UPDATE query for the Check field.

Hope that helps.