Is there any way of concatenating the columns in a crosstab query, so you can remove blank entries and add the contents into one column entry, sort of like an old fashioned string
array. It is easy when you export to Excel, and then use TRIM(CONCATENATE(A1,B1,C1...)) to add the fields together, but can't see anyway to do it using Access.

The Crosstab brings up the required matrix I am after, and it can be srted in Excel after cut and paste:

ID A B C D ConcField
1 Blue Red Green Blue Red Green
2 Red White Red White
3
4 Blue Green White Blue Green White

5
6 Blue Blue


Words are generic, and it may not be possible, just a useful tool to have in the armoury if there is some way of doing it. I just came across an excellent piece of code on one of the
web forums which removes duplicates from a list, which the crosstab is then derived from. Really good way to strip out dupes without using make table and append queries and any
intervention.

Thanks

J




--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

On 25/10/2010 22:35:45, bezz wrote:
See my post of 24th September. Had the same problem, but as yet not solved.
Because I wanted to use it with mailmerge, I have to find a way of solving
the problem using only built in functions, no user defined funtions. Got it
to work with about 7 names (colours) but kept getting "Query too complex"
errors if there were more that 7 names.

Wish you luck

What was the website you refered to please

Phil

bezz wrote:
I doubt you can edit the cross tab query as it has a groupby clause.
Maybe save it to a table (MakeTable) query, add another field to it
using AlterTable (see Help topic), then run someroutine like the below

Public Sub Concat()
Dim r As Recordset
Dim s As String
Dim iFC As Integer

'substitue Table1 with your table name
Set r = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
iFC = r.Fields.Count - 1

'loop thru all records
Do While Not r.EOF
'create concatenated string
s = ConcatF(r, iFC)

'display it
MsgBox s

'next record
r.MoveNext
Loop

r.Close
Set r = Nothing

End Sub

Private Function ConcatF(r As Recordset, iFC As Integer) As String

Dim IFL As Integer
Dim s As String

For IFL = 1 To iFC

'add to concatenated string those with a value
If Not IsNull(r(r.Fields(IFL).Name).Value) Then
s = s & r(r.Fields(IFL).Name).Value & ", "
End If
Next

'return concatenated string
ConcatF = Left(s, Len(s) - 2)

End Function

Unfortunately, using a make table crosstab just gives you a two field table (unless I am doing something wrong. The export to excel works ok, but I like to try and keep as much as I
can in house...

Previous poster asked for website of code for delete duplicates. Unfortunately cleared the history, but this is the code.

Just put this into a command button or inside a sub and call it on an on open event or similar.


Notes:
1. You can use as many fields as you like to set duplicate criteria.
2. When I created my original table before doing the dupe check, you need to have them sorted so that duplicates will end up in sequence, as it compares the record with the next one
Understandable when you look at the code but not explicit in description.
3. The original poster had a Restore Subroutine included so you got the option to restore original. My application creates the table from other data before running the query, so
running again would create the table so no need to have that option.

Unfortunately can't credit originator as I have lost the page, but "Good work Fella" wherever you are..


DoCmd.SetWarnings False

Dim db As DAO.Database, rst As DAO.Recordset
Dim strDupName As String, strSaveName As String

Set db = CurrentDb()
Set rst = db.openrecordset("your_table_name")

Stop

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
If strDupName = strSaveName Then
rst.Delete
Else
strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
End If
rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing


End If
DoCmd.SetWarnings True


--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

On 26/10/2010 22:46:07, bezz wrote:
The basic principal is to use code to build a Crosstab query and then label
the fields say Col_0, Col_1, Col_2 etc. Then use code to find the number of
fields name Col_... to build a second query based on the Crosstab query. The
concatetenated field will look something like IIf(Not IsNull(Col_0), Col_0) &
IIf(Not IsNull(Col_1), " " & Col_1) ...

Phil

bezz wrote:

If I have a crosstab called XTab1, I then create a make table query with
Xtab1 as the tablename. Ex:
SELECT Xtab1.* INTO Junk FROM XTeb1;

If you make XTab1 a maketable you won't get your results.