If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
LOOP WITHIN A LOOP
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....... |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
loop and end loop | Bob W | General Discussions | 1 | November 17th, 2005 04:32 AM |
Loop Problems | Debra Farnham | General Discussion | 1 | July 1st, 2005 03:34 AM |
Records in relational tables | Shawn | Database Design | 5 | June 18th, 2005 12:47 AM |
Newbee - how to loop through table and delete unwanted records | Newbee Adam | New Users | 2 | March 8th, 2005 09:33 PM |
How to write a nested loop using sql cursors | Paul | Using Forms | 3 | January 12th, 2005 01:13 PM |