Totals Query - Combining -not- Totalling

[Cameroncha]

I am trying to take duplicate information and combine it together into on single value seperated by spaces or commas. I haven't quite grappled the concept of a totals query.

It seems like this is what i need but instead of a total number of occurances, i want to take those occurances and combine them into a single field using spaces or commas to seperate them.

I figured out how to run a totals query that tells me how many times a Sku row is duplicated in Query.

Sku CrossSell
SK621 ls108hb
SK621 sk222
SK621 SK970DVD
SK621 SK970-PAL

Sku CountOfCrossSell
SK621 5

What i want to do instead is consolodate SK621 to occur once, then store ls108hb, sk222, sk970dvd, sk970-pal in a single value seperated by spaces or commas. How would i approach this ?

So what i would like to have is something like this:

Sku CrossSellCombined
SK621 ls108hb, sk222, sk970dvd, sk970-pal

Thanks, i just dont know how to accomplish this, is it even with a totals query?

 

[Jon K]

You will need to create a public function and then use the function in a total query.

Create a public function getCrossSell() in a Module:-
Open a new Module. Type/paste an appropriate version of the function below in the module (Public Function ... End Function inclusive), using the correct table name in the SQL string. Save the module as any name you like.

---------- Access 97 ----------------
Public Function getCrossSell(strSku As String) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Dim strCrossSell As String

SQL = "Select CrossSell FROM [TableName] where Sku='" & strSku & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

Do While Not rs.EOF
strCrossSell = strCrossSell & rs!CrossSell & " "
rs.MoveNext
Loop

getCrossSell = Trim(strCrossSell)
End Function
---------------------------------------

---------- Access 2000/XP -------------
Public Function getCrossSell(strSku As String) As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim strCrossSell As String

SQL = "Select CrossSell from [TableName] where Sku='" & strSku & "'"

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
strCrossSell = strCrossSell & rs!CrossSell & " "
rs.MoveNext
Loop

getCrossSell = Trim(strCrossSell)
End Function
---------------------------------------


Create a new query (type/paste in the SQL View of a new query, using the correct table name):-

SELECT Sku, getCrossSell(Sku) AS CrossSellCombined
FROM [TableName]
GROUP BY Sku
HAVING Count(*) >1


If you want to include in the query result those Skus that don't have duplicates, just remove the words HAVING Count(*) >1

Run the query.

 

[Cameroncha]

GREAT... Thank you so much.... i am not sure what a 'public function is' but i will try to tackle this..... thank you again >>>

I was beginning to think that the overall design of my database was faulty and began to think that i should have built the database to hold this information 'horizontally' instead of 'vertically' (make static CrossSell1 CrossSell2 CrossSell3 ect)