Cross tab or Pivot table with two data values

[Heidrun Terasa]

Hello,

I have a simple table with the fields
OBJECT_NAME
OBJECT_REMARK
ATTRIBUTE
VALUE_A
VALUE_B

ATTRIBUTE contains some different values, the same for several
OBJECT_NAMES; there can be more records for one particular OBJECT_NAME
having different ATTRIBUTE values.
VALUE_A and VALUE_B are existing definitely, and represent two numeric
values belonging to the attribute.

Example records:
OBJECT_NAME OB._REM. ATTRIB. VALUE_A VALUE_B
Object 1 Remark Attr 1 100 150
Object 2 Remark Attr 1 110 120
Object 3 Remark Attr 1 90 135
Object 4 Remark Attr 1 97 121
Object 1 Remark Attr 2 137 96
Object 3 Remark Attr 2 140 99
Object 4 Remark Attr 2 130 100
Object 5 Remark Attr 2 96 132
....

I want to get a report like this:

Attr 1 Attr 2
Object 1 Remark 100 150 137 96
Object 2 Remark 110 120
Object 3 Remark 90 135 140 99
....

The numeric values shall not be aggregated.
Using the Layout Pivot table for the data table I get
the correct view, but I am not able to create the desired report.
When I try to create a cross tab query as base for the report,
I get the message that I must not use two value fields.

I am not very experienced in Access or VisualBasic, so don't mind,
if there is a trivial solution which I didn't saw. And, I hope
I don't have to use VB. (In fact, it is a problem of a collegue,
which has yet no experience with Access at all).

Heidrun

 

[KARL DEWEY]

Try this --
TRANSFORM First([VALUE_A] & " " & [VALUE_B]) AS Expr1
SELECT Heidrun.[OBJECT_NAME], Heidrun.[OBJECT_REMARK]
FROM Heidrun
GROUP BY Heidrun.[OBJECT_NAME], Heidrun.[OBJECT_REMARK]
PIVOT Heidrun.[ATTRIBUTE];

 

[Heidrun Terasa]

Try this --
TRANSFORM First([VALUE_A] & " " & [VALUE_B]) AS Expr1
SELECT Heidrun.[OBJECT_NAME], Heidrun.[OBJECT_REMARK]
FROM Heidrun
GROUP BY Heidrun.[OBJECT_NAME], Heidrun.[OBJECT_REMARK]
PIVOT Heidrun.[ATTRIBUTE];

Thanks, Karl, I will try this.

Heidrun

 

[Heidrun Terasa]

This solution works well, and I will find out,
how to format the values. Another question:
Do I have to design the report newly, each time I
have changed the data?

Try this --
TRANSFORM First([VALUE_A] & " " & [VALUE_B]) AS Expr1
SELECT Heidrun.[OBJECT_NAME], Heidrun.[OBJECT_REMARK]
FROM Heidrun
GROUP BY Heidrun.[OBJECT_NAME], Heidrun.[OBJECT_REMARK]
PIVOT Heidrun.[ATTRIBUTE];

Thanks, Karl, I will try this.

Heidrun