Finding first occurrences in a query 1

[DPrent]

Hi Could someone please help me. I have the following data structure (this example is a small selection of data and there will be several thousands of records within the database)

"id" "badge" "Date" "Match"

35 "01444" 16/02/2004 "yes"
36 "01444" 17/02/2004
37 "01444" 29/03/2004 "yes"
38 "01444" 30/03/2004 "yes"
39 "01444" 31/03/2004 "yes"
40 "01444" 01/04/2004 "yes"
41 "01444" 02/04/2004
42 "01444" 05/04/2004 "yes"
43 "01444" 06/04/2004 "yes"
44 "01444" 07/04/2004 "yes"
45 "01444" 08/04/2004 "yes"
46 "01444" 21/04/2004 "yes"
47 "01444" 22/04/2004 "yes"
48 "01444" 23/04/2004
49 "01444" 06/05/2004 "yes"

What I am trying to do is in a query select all the first occurrences of the “Match” fields that are equal to “yes” either output the complete record or mark the record with an identifier so that I could write another query to select those records. With the above records I would expect 4 (four) marked or output records. As follows:


"id" "badge" "Date" "Match"

35 "01444" 16/02/2004 "yes"
37 "01444" 29/03/2004 "yes"
42 "01444" 05/04/2004 "yes"
49 "01444" 06/05/2004 "yes"

I am an old cobol programmer and this would be very easy to do, but I must admit I am having trouble in Access…………….Thanks for any help
Kind Regards

Dave

 

[M8KWR]

I have a method, and i have tested it, and it does work.

What to do:

1 - add another field to your table, called "Occur" (text)

2 - create a form, bound the table or query with the data you have above.

3 - create a new text box, i've called it txtFirst1, for my example, do not bound this to anything.

4 - create a command button, on the on_click procedure insert this code: -

Private Sub loop1()


Dim MaxNumber, txtFirst1

MaxNumber = Me.RecordsetClone.RecordCount

txtFirst1 = "1"

Dim Check, Counter
Check = True: Counter = 0   ' Initialize variables.
Do  ' Outer loop.
    Do While Counter < MaxNumber   ' Inner loop.
        Counter = Counter + 1   ' Increment Counter.
        

          
        If Match = "Yes" And txtFirst1 = "1" Then

        Occur = "Yes"

        txtFirst1 = ""

        Else


        End If


        If Match = "" Or IsNull(Match) Then

        txtFirst1 = "1"

        End If

        
        If Counter = Me.RecordsetClone.RecordCount Then   ' If condition is True.
            Check = False   ' Set value of flag to False.
            Exit Do ' Exit inner loop.
            
         Else
         
            DoCmd.GoToRecord , , acNext 'move to next record
            
        End If
    Loop
Loop Until Check = False    ' Exit outer loop immediately.


Me.TimerInterval = "0"


End Sub

5 - open the form, and click the command button, depending on how much data you have, unsure how long it will take, but make sure you have the data sorted in the correct order, or it will giv you false readings.

I hope this helps, and further information please let me know.

 

[DPrent]

Thanks for your idea, I did exactly as you said and on entering the button the program processed all the records. The only thing was that my table called “STD Master Update” didn’t update in the new “occur” field with yes. Any Ideas? Many Thanks…….Dave

 

[M8KWR]

Without seeing you file, no i don't.

Make sure that the field is spelt correctly, as in the code.

If it looping through the records, then break the code, and try and update the occur field yourself, and see if that works, if not you have a problem.

To be honest it does not matter what the table is called, because it jsut looks at the fields that are bound to the form. Is the occur fields on the form, if not put it on there, same as the Match field.

Hope this helps.

 

[DrSimon]

As an alternative idea you can do this as a query.
Create a query with a calculated field which is id-1. Then do an outer join between this query and your table between id-1 and id, Where Match in the Query is "Yes" and Math in the Table is null.
Hopefully your table uses True/False rather than "yes" - if it doesn't I suggest you make it so to make it more robust. In that case the criteria for Match in the Query should obviously be True.
Simon Rouse

 

[DPrent]

Fixed it, You were right I didn't have the "match" field on the form. Thank you very much for your help. One last question. As you know I'm learning Access slowly, How do I go into Debug when the program is running. Once again Thanks very much and kind regards Dave

 

[M8KWR]

the best way i find is where you want to debug it, i.e on a command button click procedure, go into the code and to the left of the code click the mouse button to highlight the text red. once you have done this, when the code is run you will be taken to the debug windows.

Hope this helps.

If you want any more help please do not hesitate to ask.

Kevin