Special loop with insert query

**ADezii** · Apr 3 '13, 01:22 PM

I am not sure concerning an SQL based solution to this problem, but the same approach as used in this Thread can be used here.

http://bytes.com/topic/access/answers/948349-column-values-rows

You could generate a Recordset based on Distinct Values for Fields [A], [B], [C], and [R]. You could then Loop through this Recordset and concatenate the Comments for each unique combination. The Results could then be written to a Results Table. This approach is a little far fetched, so be patient and see if any other, more efficient, SQL based solutions arise.

**julienmy5757** · Apr 3 '13, 01:32 PM

For the moment I have a query, a recordset to create the table and after I have this

Code:

MyTable.MoveFirst
Do While Not MyTable.EOF
        If IsNull(Me.Description.Value) = False Then
        strDescription = LCase(MyTable![Description])
        E = Me.Description.Value
        E = A & " , " & strDescription
        Else
        strDescription = LCase(MyTable![E])
        E = strDescription
        End If
        MyTable.MoveNext
    
Loop

But I am using a control in my form and I don't want it
i don't understand how can I use your code ( in the other post )

**julienmy5757** · Apr 3 '13, 01:43 PM

Oh sorry I made a BIG mistake in my post

A,B,C,R are in a query ( to select informations )
A,B,C,D,E in a table ( to take description based on A,B,C)

Can I use a query in a openrecordset too ? Because I would like to link description and the query before to run the code to have all the descriptons in the same case

**Seth Schrock** · Apr 3 '13, 01:44 PM

Based on your example at the end, are you trying to bundle the comments (E) where A, B, C & R are equal? You would need to create two separate recordsets and then loop through recordset 2 inside a loop of recordset 1. Recordset 1 would be populated by a SELECT DISTINCT query to get the unique values of A, B, C & R. You would then use these values as the criteria to get the E field in recordset 2. Recordset 2 would then loop through each record that matches the criteria and concatenate all the fields together. This might be hard to understand without an example, so here is what I put together.

Code:

Dim db As DAO.Database
Dim strMain As String
Dim rstMain As DAO.Recordset
Dim strSub As String
Dim rstSub As DAO.Recordset
Dim strComment As String
Dim strInsert As String

DoCmd.SetWarnings False

Set db = CurrentDb

strMain = "SELECT DISTICT A, B, C, R FROM MyTable"
Set rstMain = db.OpenRecordset(strMain, dbOpenDynaset)

With rstMain
	Do While Not .EOF
		strSub = "SELECT * FROM MyTable WHERE " & _
				 "A = " & !A & " AND B = " & !B & _ 
				 " AND C = " & !C & " AND R = " & !R
		Set rstSub = db.OpenRecordset(strSub, dbOpenDynaset)
		strComment = ""
		
		Do While Not rstSub.EOF
			strComment = rstSub!E & ", "
			rstSub.MoveNext
		Loop
		
		strComment = Left(strComment, Len(strComment) - 2)
		
		strInsert = "INSERT INTO NewTable (A, B, C, R, E) " & _
					"VALUES (" & !A & ", " & !B & ", " & !C & ", " & !R & ", " & strComment & ")"
		
		db.Execute strInsert, dbFailOnError
		
		.MoveNext
	Loop
End With

DoCmd.SetWarnings True

This is totally air code, so there might be a few missing characters, but that should be relatively simple to troubleshoot.

Wow, seems I should refresh before posting. Didn't see the previous posts.

**Seth Schrock** · Apr 3 '13, 01:51 PM

Yes, a query can be used in the OpenRecordset.

**julienmy5757** · Apr 3 '13, 01:59 PM

Hello Seth , again you ! aha

Lign 24 to 26 the code will put in the same case only the desc for a specific A,B,C,R and not for all ?
And, I think I have to put also D in the table to order by before to do the loop ( it is the order of comments )

How can I use a query to say for my first table
Mytable= Query
?

**Seth Schrock** · Apr 3 '13, 02:06 PM

MyTable = Query should work fine. Lines 24 to 26 will put in the same case only for a specific A,B,C,R, but since it is in a loop as well, it will go through all the specific A, B, C, & Rs.

**julienmy5757** · Apr 3 '13, 02:10 PM

I don't undestand how with a simple loop the description can go in a specific case for each A,B,C,R and make the difference when A,B,C,R are not the same

**Seth Schrock** · Apr 3 '13, 02:22 PM

Because I did a SELECT DISTINCT on A, B, C & R, I get a single record for each unique value of A, B, C & R, just like you have at the bottom of your OP. These values are then used as the criteria to get all the E fields that have A, B, C & R matching the values in the first record of the SELECT DISTINCT query. After the sub query is done looping through all the E's, it does the INSERT QUERY. The main loop then goes to the next record in the SELECT DISTINCT query and the whole process happens again, this time with new values for A, B, C & R and thus, new values of E.

**julienmy5757** · Apr 3 '13, 03:15 PM

I undestand now ! That's sound great. I will try it.

If I want to delete records before to put information in the table do I have to run a delete query in the loop or out ? I think if I put it out I have to say

Code:

strDeleteQuery= "DELETE * FROM tblOrigin WHERE " & _ 
                 "A = " & !A & " AND B = " & !B ";
 db.Execute strDeleteQuery, dbFailOnError

Syntax is correct ?

**Seth Schrock** · Apr 3 '13, 03:30 PM

You missed the ending double quote at the end of line 3. Also, don't you want to check for C and R as well? Yes, you would put it outside the rstSub loop and inside the rstMain loop. Personally, I would put it right after strInsert is executed. That way, if there was a failure inserting the records, the code would stop before the records got deleted.

**julienmy5757** · Apr 3 '13, 03:37 PM

I delete all the records based on A and B because I am not sure to have the same number of records with the same C or/and D..
I think I wil put something like if we have records in the original table for this A and B, delete it and insert new records, if not just insert.

What function permit to know if a record exist for A and B? Dlookup?

**Seth Schrock** · Apr 3 '13, 03:48 PM

I'm not going to be available much after this as I just got Monday set as a deadline for a database that I'm working on and I have a ton of work/testing left to do on it.

In looking at your sample data in the OP, the first time through the main loop would grab the first three records because those are the records that would be combined in the SELECT DISTINCT query. If you then just delete records based on A and B, every single record would be deleted even though none of the other records had been through the second loop. I suggest running through your database using SELECT queries to see what records you would be deleting each time you go through the loop so that you don't start losing data by accident.

**zmbd** · Apr 3 '13, 06:00 PM

This is a function that I've based such things on in the past: access.mvps.org - Modules: Concatenate fields in same table

There's also a way I've seen using a make table and an update query; however, I don't think that there's much improvement in that approach vs. the VBA.

To use the function however, you're more than likely going to need to do some major modifications to account for the multiple fields ([A] thru [R]) used to establish your unique records.