Columns To Rows, no numbers

[c]

I have a query that looks like this:

Ord # Item Qty
100 B-Plan 2
100 CP-PO 1
100 BP-OP 2

I want to get the Item field turned into a single row by Ord #, like this:

Ord# Item1 Item2 Item3
100 B-Plan CP-PO BP-OP

I tried using crosstab query but I can only produce this:

Ord# B-Plan CP-PO BP-OP
100 2 1 2 - which I don't want.

Is there any way to get text in a crosstab query?

Thanks, c

 

[Duane Hookom]

Is there any field that determines which item is 1, 2, or 3? You would need
this field to create the appropriate crosstab column name using DCount() or
some other expression.

 

[c]

Yes, there is SysLnSq (the order line number). I tried a crosstab using
this, but I get this:

Ord# 1 2 3
100 1 1 1 - this row shows up as the count of Item

here's what I did:

TRANSFORM Count(OrderTable.Item) AS CountOfItem
SELECT OrderTable.[Ord#]
FROM OrderTable
GROUP BY OrderTable.[Ord#]
PIVOT OrderTable.SysLnSq;

I think CountOfItem is where I need to do something different??

Thanks,c

 

[Duane Hookom]

You column heading would be something like:
ColHead: "Item" & DCount("*","OrderTable", "SysLnSq<=" & [SysLnSq])
Set the value to First of Item.

--
Duane Hookom
MS Access MVP

Yes, there is SysLnSq (the order line number). I tried a crosstab using
this, but I get this:

Ord# 1 2 3
100 1 1 1 - this row shows up as the count of Item

here's what I did:

TRANSFORM Count(OrderTable.Item) AS CountOfItem
SELECT OrderTable.[Ord#]
FROM OrderTable
GROUP BY OrderTable.[Ord#]
PIVOT OrderTable.SysLnSq;

I think CountOfItem is where I need to do something different??

Thanks,c

Is there any field that determines which item is 1, 2, or 3? You would
need
this field to create the appropriate crosstab column name using DCount()
or
some other expression.