I am trying to create a loop that will isolate all the records with same ID (ie, 1), perform a task and then move on to the next set of records that have same ID (ie, 2). I would appreciate if anybody could tell me how to do it.
Thank you.
Sam
I am trying to create a loop that will isolate all the records with same ID (ie, 1), perform a task and then move on to the next set of records that have same ID (ie, 2). I would appreciate if anybody could tell me how to do it.
Thank you.
Sam
Hi
I would create a query with criteria that returns all the required records. Create a recordset based on this query and loop thru the recordset until EOF is true. Carry out your "tasks" in each loop before moving on to the next record.
What are the "tasks" that need to be carried out? Mayby this could all be done with an "Update Query" ?
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Bob,
Thanks for the quick response. I have attached a dummy database to explain what I am trying to do.
1. Grab all the records with same id (say ID=1)
2. sort "FromLeft" in ascending order,
3. compare "FromLeft" value with "ToLeft" value of previous record
4. if "FromLeft" value <= "ToLeft" value of previous record, export both records to a new table
5. Once this is done for all the records with ID=1, move on to next set of records with same id, (say ID=3)
Its even better if this can be done using query.
Thank you.
Sam
Hi
What will the "FromLeft" value of the first record be compared with3. compare "FromLeft" value with "ToLeft" value of previous record
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Is it possible to ignore or skip the first record of each set?
Hi
I think that skipping the first record should be possible, as is the rest of your task, but it will involve some careful thought and code.
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
So, I'm guessing creating a query to do this is out of the question. I'm new to programming. Would you have time to help me figure this out?
Thank you.
Hi sam10
.So, I'm guessing creating a query to do this is out of the question
IMHO that is true.
I'll have a try and see how I get on.Would you have time to help me figure this out?
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Bob,
Have you had time to look into it? I didn't mean to bug you but I need to find the solution soon.
Thank you.
Samir
Hi
I've made some good progress but not finished yet. I'm sorry to say that I've had other personal and work related issues that needed all my time since my last post. Hope to have a solution for you in the next 24hrs.
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Hi
You may like to try this. Hopefully it will give the required results.
The function listed below should be copied to a general module so that it can be called from anywhere in your db.
Code:Public Function fnBobsSolution() On Error GoTo Err_fnBobsSolution_Error Dim Db As Database Dim recMultiIDs As DAO.Recordset Dim sqlRecMultiIDs As String Dim SqlAppend As String Dim lngRC As Long Dim lngID As Long Dim lngOBID As Long Dim recIDs As DAO.Recordset Dim sqlRecIDs As String Dim varFromLeft As Variant 'Returns a list of IDs that are in the table more than once sqlRecMultiIDs = "SELECT Table1.ID, Count(Table1.ID) AS CountOfID " & _ "FROM Table1 " & _ "GROUP BY Table1.ID " & _ "HAVING (((Count(Table1.ID)) > 1)) " & _ "ORDER BY Table1.ID;" Set Db = CurrentDb Set recMultiIDs = Db.OpenRecordset(sqlRecMultiIDs) With recMultiIDs 'Loop thru the multiple IDs Do Until .EOF lngID = .Fields("ID") 'Returns all records from Table1 with the current ID sqlRecIDs = "SELECT Table1.OBID, Table1.ID, Table1.FromLeft, Table1.ToLeft " & _ "FROM Table1 " & _ "WHERE (((Table1.ID) = " & lngID & ")) " & _ "ORDER BY Table1.OBID;" Set recIDs = Db.OpenRecordset(sqlRecIDs) With recIDs .MoveLast lngRC = .RecordCount 'Go to the last record. Set the value of varFromLeft to that of "FromLeft". Move to the previous rec. 'Compare the value of varFromLeft to that of "ToLeft". For lngRC = lngRC To 1 Step -1 If Not IsEmpty(varFromLeft) And varFromLeft <= .Fields("ToLeft") Then SqlAppend = "INSERT INTO Table1CopiedRecords ( OBID, ID, FromLeft, ToLeft, FromRight, ToRight, Flag ) " & _ "SELECT Table1.OBID, Table1.ID, Table1.FromLeft, Table1.ToLeft, Table1.FromRight, Table1.ToRight, Table1.Flag " & _ "FROM Table1 " & _ "WHERE (((Table1.OBID)=" & .Fields("OBID") & "));" Db.Execute SqlAppend SqlAppend = "INSERT INTO Table1CopiedRecords ( OBID, ID, FromLeft, ToLeft, FromRight, ToRight, Flag ) " & _ "SELECT Table1.OBID, Table1.ID, Table1.FromLeft, Table1.ToLeft, Table1.FromRight, Table1.ToRight, Table1.Flag " & _ "FROM Table1 " & _ "WHERE (((Table1.OBID)=" & lngOBID & "));" Db.Execute SqlAppend End If varFromLeft = .Fields("FromLeft") lngOBID = .Fields("OBID") .MovePrevious Next End With varFromLeft = Empty lngOBID = 0 .MoveNext 'Go to the next ID in list Loop End With Exit_ErrorHandler: 'Clean Up Set Db = Nothing Set recMultiIDs = Nothing Set recIDs = Nothing Exit Function Err_fnBobsSolution_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnBobsSolution of Module Module1 at Line " & Erl Resume Exit_ErrorHandler End Function
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Bob,
It did what I exactly wanted it to do. So, I guess I would just have to copy and paste the codes to do the same to "FromRight" and "ToRight" fields and also create an empty table with same schema.
Thank you very much.
Sam
Hi Sam
Glad it worked for you and sorry I had to keep you waiting so long.
If you copy/paste the function to do the same for "FromRight" and "ToRight" fields, then you will need to go thru the code and replace "FromLeft" with "FromRight" and replace "ToLeft" with "ToRight". Don't forget to change the name of the second function.
If you have any probs, post back.
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick