I can't get the data to display as I want it. I haven't done a lot of Crosstab queries, so I need someone to clarify the field settings....


I have Pallet ID's which contain Multiple UNIT ID's


so this...

PALLET-1 UNITID-1
PALLET-1 UNITID-2
PALLET-1 UNITID-3
PALLET-1 UNITID-4
PALLET-1 UNITID-5
PALLET-1 UNITID-6
PALLET-1 UNITID-7
PALLET-1 UNITID-8
PALLET-1 UNITID-9
PALLET-1 UNITID-10

should look like....

PALLET
PALLET-1 UNITID-1 UNITID-2 UNITID-3 UNITID-4 UNITID-5 UNITID-6 UNITID-7 UNITID-8 UNITID-9 UNITID-10


the unit ID's must be on the same row as the pallet record, it can be a string in one cell

TRANSFORM UNITID
SELECT PalletID
FROM Table1
GROUP BY PalletID
PIVOT UNITID;


HTH:D

Thanks Guus, I will try it out....I have family in Vaassen (mom was born there)....not too far from you.

GUUS, that is the same as what I was trying also, but in design view so I did not recognize the SQL, but it does not work...see attached.

I actually had to delete most of the data to get the crosstab to show results, so crosstab may not be the right method? Unless there is another way?

the first column is the pallet ID, so I want to list each Unit ID to the right, but the crosstab creates a new column for each unit so that is no good for me. I think I need VBA?

It is a small world! I'll say hi to your mother!


The pivot query does what it is designed to do. If you want this result:

PALLET-1 UNITID-1 UNITID-2 UNITID-3 UNITID-4 UNITID-5 UNITID-6 UNITID-7 UNITID-8 UNITID-9 UNITID-10
PALLET-2 UNITID-1 UNITID-6 UNITID-7 UNITID-8 UNITID-9 UNITID-10
PALLET-3 UNITID-1 UNITID-2 UNITID-5 UNITID-6 UNITID-9 UNITID-10
You can accomplish that by using VBA. As you suggested:
Public Sub CreateOutput(rst As Recordset)

Dim intRij As Integer
Dim intVelden As Integer
Dim intTeller As Integer

If rst.EOF Then
MsgBox "Geen records gevonden voor " & rst.Name, vbExclamation, Application.Name
Exit Sub
End If

intVelden = rst.Fields.Count - 1

Do While Not rst.EOF
For intTeller = 0 To intVelden
If Len(Nz(rst.Fields(intTeller))) > 0 Then
Debug.Print rst.Fields(intTeller) & Space(1)
End If
Next intTeller
rst.MoveNext
Loop

Set rst = Nothing

End Sub
This code was not debugged.

Enjoy!