LOOP WITHIN A LOOP

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 Bad10
UNION ALL
SELECT Q_FindFaults.ContactList_ID, Q_FindFaults.Bad2
FROM Q_FindFaults WHERE Bad20
UNION ALL
SELECT Q_FindFaults.ContactList_ID, Q_FindFaults.Bad3
FROM Q_FindFaults WHERE Bad30;

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
Please feel free to quote anything I say here.


IfOnlyIKnewCode wrote:

Good morning ladies and gentleman. First Of all, a big thank you to everyone
who has kindly given me advice and support in regards to my question. I did
previously pose the question as (BASIC CODE TO USE IN MY FIRST DATABASE but I
think the question implied that I was being lazy and wanted some one to do
all the work for me. Not the case, I have just found it hard knowing what
syntax to use for code. I have looked for a good book to guide me through
writing VB in accedss for beginners but can not find one. If anyone knows
of a good book which I can order on the internet to teach me properly I would
really appreciate it. Ps I live in the UK. In reagrds to my question which
I think is the engine to the whole database I need to write;

Chris2 - I really don ot know how I can achieve this with a single update
statement. In essense my database will work by;

Comparing a list of records containing contact addresses against a new file
of contact addresses with the "new" records being the ones to validate.

The fields will be along the lines of;

Contact Name, Address 1, Address 2, Address 3 Tel No, Post Code, Fax No

The reason I asked the loop within a loop question was so that I can loop
through all of the fields for each record anh have a running score. Eg

Outer Loop From 1 to end of new records
No Of New Records Counter=0
If outerloop record is not duplicate add 1 to New Records Counter

Inner Loop From Field 1 to End Of fields
DataScore=20 (20 Fields To Validate in total so 20 is potential best
score)

VALIDATION CHECKS
Compare Value for Outerloop, innerloop in forbidden values

John Vinson, looking at your reply - it has got me thinking, a simple query
links one table to another e.g. Customer No to Customer No, I guess I could
link the forbidden values table to the values of inner loop
(rs.fields(i).name and outer loop by just using as the criteria??

If the value matches up to a value in forbidden values then
DATASCORE=DATASCORE-1
NEXT INNER LOOP

RECORD SCORE IN TABLE (so that reporting can be done)

NEXT OUTERLOOP

And thats it...easy in theory...ha ah. Thanks again, I have slowly but
surely gaining confidence from all of the input and I hope in time might be
able to offer the same help to others.

Thank Vincent for your time and understanding. Your help is really
appreciated and you are a credit to this site.

Thanks again, I will disect your code and plan my database
accordingly...Very nervous I have to say ha ha

"SEAN DI''''ANNO" wrote:

Good morning,

I am using Access 2000 and just about to write my first database. I have
some code for looping through the records in a certain field but is it
possible to have an inner loop which will lop through the reords for the
first field then go to the next field and lop again and so on.......