Assuming you can define a set of forbidden values, a Query (or set
thereof) is probably the easiest way to go. But your criteria could be
trickier than just an easily listed set. I'll show an example using two
ways to calculate your scores. Suppose you have a Table looking like
this (I've omitted some fields for brevity):
[ContactList] Table Datasheet View:
ContactList_ID Contact Name Address 1 Post Code
-------------- --------------- ---------- ---------
2085843127 Murgatroyd 23-B Baker 2V5-L8N
2014364093 Rumpelstiltskin 85 Palace 0H1-B0Y
-1397802452 Smith 9 Downing 2V5-X2Z
-1488856006 xxx None 0X0-X0X
There are some things you might not like about some of these records, as
you indicate in the following Query. For example, you are critical of
anyone whose name begins with "X". (Incidentally, please remember that
in all of this, no amount of consistency checking will ever ensure that
the data that you enter are correct. What it will do is catch obvious
mistakes. If you COULD somehow identify correct records, you wouldn't
need to input them, you could just calculate them.)
In your case, you'd likely have lots of other criteria to list here.
[Q_FindFaults] SQL:
SELECT ContactList.ContactList_ID,
(IIf((Right$([ContactList]![Contact Name],5)="troyd")
Or (UCase$(Left$([ContactList]![Contact Name],1))="X"),-1,0)) AS Bad1,
IIf(InStr([ContactList]![Address 1],"Baker")>0,-1,0) AS Bad2,
IIf(Left$([ContactList]![Post Code],3)="2V5",-1,0) AS Bad3
FROM ContactList;
Hmmmm... it seems we have some apparent mistakes here.
[Q_FindFaults] Query Datasheet View:
ContactList_ID Bad1 Bad2 Bad3
-------------- ---- ---- ----
-1488856006 -1 0 0
-1397802452 0 0 -1
2014364093 0 0 0
2085843127 -1 -1 -1
What I do next is to tabulate these into two columns so that I can add
them up easily.
[Q_FindFaults2] SQL:
SELECT Q_FindFaults.ContactList_ID, Q_FindFaults.Bad1 as Bad
FROM Q_FindFaults WHERE Bad1<>0
UNION ALL
SELECT Q_FindFaults.ContactList_ID, Q_FindFaults.Bad2
FROM Q_FindFaults WHERE Bad2<>0
UNION ALL
SELECT Q_FindFaults.ContactList_ID, Q_FindFaults.Bad3
FROM Q_FindFaults WHERE Bad3<>0;
Notice that a record might have multiple mistakes.
[Q_FindFaults2] Query Datasheet View:
ContactList_ID Bad
-------------- ---
2085843127 -1
-1488856006 -1
2085843127 -1
2085843127 -1
-1397802452 -1
Now I add up the mistakes and subtract from the total number of fields
(4 in this case, but you'd probably use something more like 20).
[Q_FindFaults3] SQL:
SELECT Q_FindFaults2.ContactList_ID,
4+[SumOfBad] AS ScoreFromQuery,
Sum(Q_FindFaults2.Bad) AS SumOfBad
FROM Q_FindFaults2
GROUP BY Q_FindFaults2.ContactList_ID;
and the results give you a score (in the [ScoreFromQuery] field) similar
to what you described.
[Q_FindFaults3] Query Datasheet View:
ContactList_ID ScoreFromQuery SumOfBad
-------------- -------------- --------
-1488856006 3 -1
-1397802452 3 -1
2085843127 1 -3
HOWEVER... depending on how tricky your criteria are, you might prefer
to write a VBA function to do the same calculations. The following code
is faulty, however, as it omits all the error-checking statements that I
would normally include. (Some people object to including error-checking
stuff on the basis that errors shouldn't occur in the first place, and I
suppose I agree if the software is operating a traffic signal or
surgical equipment.)
Once you have written the basic function (BTW, you need to set a
reference to DAOs for this to work), adding or changing criteria is
pretty easy.
'DataScore() returns the number of valid fields in
' the selected record in the [ContactList] Table
'
Public Function DataScore( _
ByVal RecordID As Long) _
As Integer
'ByVal RecordID As Long 'Primary key of record
Dim CurrentRecord As Recordset 'Record identified _
by RecordID key value
Dim fldField As Field 'One of the data fields
'Grab the selected record
Set CurrentRecord = CurrentDb.OpenRecordset _
("SELECT * FROM [ContactList] " _
& "WHERE [ContactList_ID] = " _
& RecordID & ";", _
dbOpenSnapshot)
'Start by assuming all fields to be valid
DataScore = CurrentRecord.Fields.Count
For Each fldField In CurrentRecord.Fields
'Look for whatever might be wrong in the field
Select Case fldField.Name
Case "Contact Name"
If Right$(fldField.Value, 5) = "troyd" _
Then DataScore = DataScore - 1
If UCase$(Left$(fldField.Value, 1)) = "X" _
Then DataScore = DataScore - 1
Case "Address 1"
If InStr(fldField.Value, "Baker") > 0 _
Then DataScore = DataScore - 1
Case "Post Code"
If Left$(fldField.Value, 3) = "2V5" _
Then DataScore = DataScore - 1
End Select 'Case fldField.Name
Next fldField
End Function 'DataScore()
You can refer to your DataScore() function in SQL similarly to this:
[Q_Scores] SQL:
SELECT ContactList.*,
DataScore([ContactList]![ContactList_ID]) AS Score,
Q_FindFaults3.ScoreFromQuery AS QScore
FROM ContactList LEFT JOIN Q_FindFaults3
ON ContactList.ContactList_ID = Q_FindFaults3.ContactList_ID
ORDER BY DataScore([ContactList]![ContactList_ID]) DESC;
and the results show both sets of scores. You'll notice that one of the
[QScore] values is missing (no faults found on that record), but if you
need it you can take care of it by modifying the Query.
[Q_Scores] Query Datasheet View:
ContactList_ID Contact Name Address 1 Post Code Score QScore
-------------- --------------- ---------- --------- ----- ------
2014364093 Rumpelstiltskin 85 Palace 0H1-B0Y 4
-1488856006 xxx None 0X0-X0X 3 3
-1397802452 Smith 9 Downing 2V5-X2Z 3 3
2085843127 Murgatroyd 23-B Baker 2V5-L8N 1 1
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
