Concatenating records for export

Can you post a "mock-up" of the exported data ? Can get a more refined answer for you if you provide a bit more detail on how you want the result to look.

Depending on how many categories you have, have a play with crosstab queries (even the wizard may help to make a query you can refine). It  should give you something close to what you want.

The CSV transfer is no challenge once you have the data you want to export. There are plenty of solutions for that in EE.

Andrew

I think you need a function that loops through your recordset and does the concatenation. Pass the ID to this function, create a recordset based on this ID

Dim cnn as adodb.connection, rst as new adodb.recordset, strReturnValue As string

Set cnn = currentproject.connection
rst.open "Select * from CategoryTX Inner Join Category_List On CategoryTX.CategoryID = Category_List.CategoryID Where ID = " & lngID & " Order By ID", cnn, dbOpenStatic, dbLockReadOnly

Then you can loop through this and concatenate the way you want.

EA

you will need a function to do this something like this

Function ConcatThem(x ) As String
Dim rs As DAO.Recordset, strTemp As String
Set rs = CurrentDb.OpenRecordset("Select * from CategoryTX, Category_List.Categories Inner Join Category_List On CategoryTX.CategoryID = Category_List.CategoryID Where ID = " & x & " Order By ID")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("Categories") & "|"
      rs.MoveNext
    Loop

    ConcatThem = Left(strTemp, Len(strTemp) - 1)
End Function

Thanks guys - I'm not in front of that data at the moment so I can't test the solutions straight away but will do so at first opportunity (about 8hrs in future, at work at mo).

Where do I enter the function information? In the Builder?

@ard65 - the way I'd like the data to be in the end is (using comma for a separator as pipe is actually necessary to be included in the data);
GHCode, CombinedCategory's
MyCode1,Xbox360|Xbox360/Games|Xbox360/Games/Action
MyCode2,Xbox360|Xbox360/Accessories
MyCode3,Xbox360|Xbox360/Games|Xbox360/Games/Adventure|Xbox360/Games/Family

place the function in a module.

and use the function in a query

Sorry Capricorn I haven't used any code in Access before - how do I use the function in the query? just a matter of adding another column to a select statement in the table/column reference area?

try this query, i may have to ask you to post sample data from both tables

select ID, concatthem([id])
from [CategoryTX]
group by ID, concatthem([id])

gorlaz, capricorn1's got your solution.

Just one note. capricorn1, you forgot an alias.

select ID, concatthem([id]) As ConCatString from [CategoryTX] group by ID, concatthem([id])

Also, just curious, why are you using a Totals query?

One last question, you're using DOA and he's got A2007. Do 2007 default to ADO?

EA

DAO is the default in A2007 references.

<Also, just curious, why are you using a Totals query? >

so you will get only one line per record

Hi capricorn1 - when I try the query you posted in combination with the function I get the error - 'Run time error '3135', Syntax error in Join Operation'

I've amended the function to reflect the real column names - I didn't realise I had been so liberal with them above I'm sorry; the table names are Categories_List and CategoriesTX. Categories_List contains Category_ID and Category_Path. CategoriesTX contains ID, CategoryID and GHCode.

Function ConcatThem(x) As String
Dim rs As DAO.Recordset, strTemp As String
Set rs = CurrentDb.OpenRecordset("Select * from CategoriesTX, Categories_List.Category_Path Inner Join Categories_List On CategoriesTX.CategoryID = Categories_List.Category_ID Where ID = " & x & " Order By ID")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("Category_Path") & "|"
      rs.MoveNext
    Loop

    ConcatThem = Left(strTemp, Len(strTemp) - 1)
End Function

Example of data from CategoriesTX;
ID      CategoryID      GHCode
1      16      360Madden06
992      2      360BIAHellsHighway

Example of data from Categories_List;

Category_ID      Category_Path
2      Xbox 360
3      Xbox 360/Pre Orders

In my Categories_List table I have little '+' signs to the left where when I expand them I can see all the records that have been put under that category eg;

ID      GHCode
992      360BIAHellsHighway

I won't be able to respond for two days as I'm away for work, but will respond as soon as I'm able on my return. Any insights appreciated guys.

Hi Guys,
Any ideas as to why I'm getting the error?
Thanks

The select statement should read:

"Select *.CategoriesTX, *.Categories_List  from CategoriesTX Inner Join Categories_List On CategoriesTX.CategoryID = Categories_List.Category_ID Where ID = " & x & " Order By ID"

EA

Thanks Eantar, I'll try it in about 4 hrs.

Hi guys, no dice unfortunately.

New error is 'Run time error '3075'. Syntax error (missing operator) in query expression '*.CategoriesTX'.

Module code is;

Option Compare Database

Function ConcatThem(x) As String
Dim rs As DAO.Recordset, strTemp As String
Set rs = CurrentDb.OpenRecordset("Select *.CategoriesTX, *.Categories_List  from CategoriesTX Inner Join Categories_List On CategoriesTX.CategoryID = Categories_List.Category_ID Where ID = " & x & " Order By ID")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("Category_Path") & "|"
      rs.MoveNext
    Loop

    ConcatThem = Left(strTemp, Len(strTemp) - 1)
End Function

Query code is;
select ID, concatthem([id])
from [CategoriesTX]
group by ID, concatthem([id])

That Select statement above looks ok to me, would there be a setting or something I need to tweak or have I copied it in wrong?

gorlaz,

compact and repair db
zip your db and upload here www.ee-stuff.com

The name of your first table is CategoryTX not CategoriesTX. Replace the wrong one with the right one and it should work.

EA

File uploaded to ee-stuff.com under this question number

carpicorn1 - You're deadset amazing! Exactly what I was after, many thanks!
Finalised solution for future ref was;
Module:
Function ConcatThem(x As String) As String
Dim rs As DAO.Recordset, strTemp As String

Set rs = CurrentDb.OpenRecordset("SELECT CategoriesTx.GHCode, Categories_List.Category_Path FROM CategoriesTx INNER JOIN Categories_List ON CategoriesTx.CategoryID = Categories_List.Category_ID Where CategoriesTx.GHCode='" & x & "' ORDER BY CategoriesTx.GHCode")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("Category_Path") & "|"
      rs.MoveNext
    Loop

    ConcatThem = Left(strTemp, Len(strTemp) - 1)
End Function

Query:
SELECT CategoriesTx.GHCode, ConcatThem([GHCode]) AS CombinedCategory
FROM Categories_List INNER JOIN CategoriesTx ON Categories_List.Category_ID = CategoriesTx.CategoryID
GROUP BY CategoriesTx.GHCode, ConcatThem([GHCode])
ORDER BY CategoriesTx.GHCode;

Now I've got some research to do to find out what it all means :)

Fantastic - very grateful for the help