I have a table that stores Part # and Program Name. Each Part # can be associated to multiple Programs. Currently I use a query and VBA code to loop through the record set and combine the program names into 1 field for each part #. I have my database automated with VBA code and was conducting a time study on how long each process takes. I found the biggest hangup in my process is this loop that takes roughly 13 minutes to run.

I was wondering if there was a way to combine the Program Names by Part # into 1 field without running a loop. I was searching online and could not find anything.

Here is an example of what I want to do. I simplified the Part # and Program fields for ease of explaining:

Part/Program Table:

Part # | Program Name
1 | A
1 | B
1 | C
1 | D
2 | A
2 | C
3 | B
3 | D
4 | E


The result table would be:

Part # | Program Name
1 | A, B, C, D
2 | A, C
3 | B, D
4 | E


Like I stated earlier my current process is taking a unique part # record set and looping it through a query with VBA code to combine the Program Names. My process works but is very time consuming. I was wondering if there was a way to combine the Programs in one field without using a loop. I cannot think of a way to do so and cannot find any useful information on the internet to help out. Any input is appreciated.

Thanks.

Hi,

In my limited experience with a moderate amount of records in a table, I have found that using recordsets is usually faster than using jet queries, so I would suspect that your current method might be the better approach. However, we might need to examine how exactly are you performing the loops.

For instance, are you by any chance using this fConcatChild() function from The Access Web? If not, you might give it a try and compare the result.

Just my 2 cents...

By the way, what version of Access are you using?

This post has been edited by theDBguy: Mar 16 2011, 01:39 PM

Thanks for the quick response. I am using Access 2007.

I am not using the fConcatChild function. I actually have never heard of that function. Here is my code for the loop:

Just some general info. All tables used in the loop are stored in Access.

tbl_06b_Unique -> table my unique Part #/ Model Year are stored. Fields: Part15 and Model_Year
qry_06c_Prod -> query that needs parameters "P" - Part # and "M" - Model Year that lists all Programs for each Part #/Model Year combo. Creates table tbl_06c_Prod. Fields: Part15, Model_Year, and Combined. Note: Combined is the Program Name.
tbl_06d_Prod -> used to store concatenated Programs. Fields: Part15, Model_Year, and Combined.


Public Function ProgramLoop() As Boolean

'Declare database variables

Dim db As DAO.Database
Dim rst, rst2 As DAO.Recordset
Dim qry As DAO.QueryDef
Dim strMajorNoun As String

'Set recordsets

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_06b_Unique")
Set qry = db.QueryDefs("qry_06c_Prod")

'Turn off warnings

DoCmd.SetWarnings False

'Delete data in Program table

DoCmd.RunSQL "DELETE tbl_06d_Prod.* FROM tbl_06d_Prod;"

Do Until rst.EOF

'Delete loop data and run loop query

DoCmd.RunSQL "DELETE tbl_06c_Prod.* FROM tbl_06c_Prod;"
qry.Parameters("P") = rst!PART15
qry.Parameters("M") = rst!MODEL_YEAR

qry.Execute

'Turn on warnings

DoCmd.SetWarnings True

'Set recordset to loop data

Set rst2 = db.OpenRecordset("tbl_06c_Prod")

strMajorNoun = ""

Do Until rst2.EOF

'Store all Program data in variable

If strMajorNoun = "" Then
strMajorNoun = RTrim(rst2![Combined])
Else
strMajorNoun = strMajorNoun & ", " & RTrim(rst2![Combined])
End If

rst2.MoveNext

Loop

'Insert data into Program table

DoCmd.RunSQL "INSERT INTO tbl_06d_Prod ( [PART15],[MODEL_YEAR], [Combined] ) SELECT '" & rst![PART15] & "' AS [PART15], '" & rst!MODEL_YEAR & "' AS [MODEL_YEAR], '" & strMajorNoun & "' AS [Combined];"
DoCmd.SetWarnings True

rst2.Close
rst.MoveNext

Loop

DoCmd.SetWarnings True

End Function


That is my current process. I will have to do some research on the fConcatChild function and see if I can use that. Thanks.

This post has been edited by cjousma: Mar 16 2011, 01:59 PM

Hi,

Thanks for posting your code. It looks like your initial post was a little misleading since you seem to need two pieces of information instead of one to identify which records to combine. If that's the case, I don't think that the fConcatChild() function will work for you out-of-the-box. You will have to modify it to accept two parameters for the Primary key.

If you can do that, I would suggest that you try using a query instead of a temporary table to get your combined fields to see if there's a difference in speed.

Just my 2 cents...

Yea I changed up the requirements in the original post to make it easier for everyone to follow. I will try adjusting and using the fConcatChild function to see if that will speed up my process but just from looking at the code in the function it looks like that will take about the same amount of time as my current process. You never know until you try though.

Thanks for all of the input. I appreciate it.

This may not be having the desired effect:

Instead, try:

Dim rst As DAO.Recordset, rst2 As DAO.Recordset

You can use one Dim for several variables (I prefer one variable per line, but that's my preference, not a requirement), but if you do not specify the type for a variable it defaults to Variant.

I started writing out some code for you to use then accidentally hit tab and backspace and erased it all. Doh! I am running out of time now but I will give you the short version of what I was typing out.

Instead of creating a new table each time you wish to "group" all of the values for those two fields together, I have often used a slightly more direct approach. I create a sorted query that I use as a recordset. This "master" recordset contains all of the data I am trying to use. I then use a loop to step through each record in the recordset and check to see if the ID field (could use fields here) are the same. If they are, I add the concatenating field to the string variable with a ",". Once it detects a "new" ID, it then writes the record (using an open recordset of the destination) and then sets the held ID to the new ID and starts again.

Sorry I couldn't do all that in code now, but I due to go home.

Regards,

Kat