Hello...

This is bugging me to death...http://www.dbforums.com/db_images_v3/images/smilies/eek.gif

I need to make a query that combines data that is Un-similar...

Heres what I have

Table1:
OrgID: OrgRef:
1001 12345
1001 6789
1001 7412
1002 6581
1002 9982

I need a query to return results like this

OrgID: OrgRef:
1001 12345, 6789, 7412
1002 6581, 9982

I need this to also be dynamic as I do not know how many combos there willl be at any time..

Ive tried many different codes ive seen, but they do not seem to work...

I TRULY appreciate your help!!!!

Hello and Welcome!

Well you've started with quite a poser :D

Unfortunately, what you're asking Access to do is not within its capabilities if you're trying to do this entirely with queries. At the very least, you would have to create a custom function and combine it with a query to produce the results you're looking for.

This type of "tree-building" problem is often associated with the use of "recursive" queries, which again, Access doesn't support.

But, here's a reference to some good background information (http://www.utteraccess.com/forums/showflat.php?&Board=82&Number=1835492&page=5&view=collapsed&sb=5&o=&fpart=1)

The topic gets pretty technical, but is worth reading through to appreciate that it's a problem more than a few folks have wrestled with.

Anyway, to use the Access custom function 'cheating' method (which is very inefficient in comparison to a completely native SQL solution), you'd set up your query as so:
SELECT OrgID, OrgRef
FROM OrgTable
ORDER BY OrgID;Then you'd create a subprocedure to loop through the recordset and build your combined OrgRef field (as a string) until it encountered the next OrgID - at that point, you'd write your results into a table; 1 record per OrgID.

Hope that helps!

Regards,
John

This sounds very interesting, and I guess I should say that i am a little "n00bish" when it comes to some of the SQL/VB coding. Im trying to learn as I go, as somehow Ive become our companies database b*&^h :eek:

I understand that this is probally a bad way to begin messing with the data, but for where the data comes from and how the out put is from that program, we have no other option for formating. We are trying to make this as automated as possible.

The final destination for this data is an automated email through our Lotus Notes (I know...Notes...:() I have that part working just fine, just need to get the data broken down a little bit more as I described above.

Anyway you could elaborate on the Query loop you were describing? I truly appreciate your assistance.

Apologies for not getting back sooner - it's been a busy week where I really had to concentrate on a couple things . . .

Anyway, first you'd build a table, let's call it: tbl_OrgBundled
Give it the following fields:
OrgID (presumably "Number" data type)
OrgRefBundled (use "Text" datatype)
OrgRefCount ("Number" datatype)

Then you'd paste the following code into a Module:
Sub Write_Exceptions_To_Table()

Dim db As Database
Dim rsGet As Recordset
Dim rsWrite As Recordset
Dim varOrgID As variant
Dim varNextOrgID As variant
Dim strBuild As String
Dim intNumElements As Integer

Set db = CurrentDb()
Set rsGet = db.OpenRecordset("SELECT OrgID, OrgRef FROM OrgTable ORDER BY OrgID, OrgRef")
Set rsWrite = db.OpenRecordset("tbl_OrgBundled")

With rsGet

Do While Not .EOF
varOrgID = ![OrgID]
.MoveNext
If Not .EOF Then
varNextOrgID = ![OrgID]
Else
varNextOrgID = "EOF"
End If
.MovePrevious

strBuild = strBuild & ![OrgRef] & ","
intNumElements = intNumElements + 1

If Not (varOrgID = varNextOrgID) Then
'add record to table
strBuild = Left(strBuild, Len(strBuild) - 1)
With rsWrite
.AddNew
!OrgID = rsGet![OrgID]
!OrgRefBundled = strBuild
!OrgRefCount = intNumElements
.Update
End With
're-initialize variables
strBuild = ""
intNumElements = 0
End If

.MoveNext

Loop

End With

rsGet.Close
rsWrite.Close

Set rsGet = Nothing
Set rsWrite = Nothing
Set db = Nothing

End Sub

Whatever trigger you want to use to start that code is up to you (i.e., you can call it from a form command button, or simply call it in the VBA Immediate window, etc.)

That code will write the results you're looking for into the table you've built. I customized some pre-existing code, so it might have some glitches, but hopefully will run properly (you can Compile it beforehand to double-check).

Hope this helps!

Regards,
John

Thanks for getting back.!! This code looks like it will work for what is needed. I actually ended up also creating a Concat. Module that spliced together my data to a table, then ran another Update query to a table with specific primary keys to remove all the duplicate entrys. It works for me....I think i may attempt to re-write and use the code you provided to see if it is more efficient

Many Thanks for your help!!!!

You're welcome and glad to hear you've got something setup and working in the meantime -

I'm afraid any way you dice it, it's gonna be a lengthy proposition (including what I posted), but if it works and gets the job done on time, then everyone else is happy I suppose :)

Anyway, all the best with your database projects!

Regards,
John

I tried to use this code following my post about concatenating text fields across records. I have followed the instructions, creating an OrgTable with test data and an tbl_OrgBundled table, put the code in a module. I ran this from the immeadiate widow. But I keep getting a debug error and the line:

Set rsGet = db.OpenRecordset("SELECT OrgID, OrgRef FROM OrgTable ORDER BY OrgID, OrgRef")

highlighted in yellow. Is it a referencing issue? It's really frustrating that I don't understand enough of this yet.

Any help would be very much appreciated.

Cheers

You might need to pop a semi-colon onto the end of the SELECT statement:

"SELECT ... ORDER BY OrgID, OrgRef;"

Otherwise, double-check that your tables have the correct field names. OrgTable is your source table, tbl_OrgBundled is the table you're writing to.

If this all checks out, then explicitly dimension your recordsets as DAO.Recordsets and double-check your References to see if DAO option is checked off.

HTH,
John

You might need to pop a semi-colon onto the end of the SELECT statement:

"SELECT ... ORDER BY OrgID, OrgRef;"

Otherwise, double-check that your tables have the correct field names. OrgTable is your source table, tbl_OrgBundled is the table you're writing to.

If this all checks out, then explicitly dimension your recordsets as DAO.Recordsets and double-check your References to see if DAO option is checked off.

HTH,
John

Tried this this morning (using Access 2003) and it worked! I can only think it either doesn't work in 2002 or I was tired last night.

Many thanks

Ro

This post and replies have been super helpful to me. I'm hoping that someone see my question and can respond with some helpful tips!

So I followed the steps completely and successfully tested your method with my own data. However, when I use my full set of data, the "combined cell" ends up with too many characters and I get this error after running the module:

"Run-time error '3163': The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

Any suggestions? I'm no Access expert so I'm kinda stumped now. Any help would be greatly appreciated!

-Ben

Hi John,

Thanks for the code. I've modified it for my own database, and it's working fine, except that if I try to run it again, I get an error saying it would cause duplicate entries and so on. If it's not too much trouble, could you show me what the code would look like to clear the contents of tbl_OrgBundled, prior to populating it with the new values from orgtable? I'm just thinking that as new entries are added, it would be nice to clear out the data that is no longer relevant and only populate it with current data.

Thanks in advance!

Please ignore my previous post. After a little thought I added this if anyone is curious.

Dim mySQL As String
mySQL = "Delete * from tbl_PresentersBundled"

CurrentDb.Execute mySQL, dbFailOnError

above the "With rsGet" line.