Cross tab or Pivot table with two data values

  • Thread starter Thread starter Heidrun Terasa
  • Start date Start date
H

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
 
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];
 
KARL said:
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];
Click to expand...

Thanks, Karl, I will try this.

Heidrun
 
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?

KARL said:
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];
Click to expand...

Thanks, Karl, I will try this.

Heidrun
Click to expand...
 
Back
Top