Creating a report

[Ally H.]

I have a table designed as follows
record1 record2 record3
Name smith jones smith
Month 1 1
2
Field1 10 5
8
Field2 12 10
7
etc

I want to report like this (grouped)
month1 month2 month3 - so on
Field1 15 8
Field2 22 7

at the moment all i can get is this

month field1 field2
1 15 22
2 8 7

Any help would be appreciated.

Ally.

 

[Fons Ponsioen]

In order to do so you must first normalize the table,
You need the table in this format:
Lname text
Month Number
Field Number
Qty Number
That results in a table that looks like:
LName Month Field Qty
Smith 1 1 10
Jones 1 1 5
Smith 2 1 8
Smith 1 2 12
Jones 1 2 10
Smith 2 2 7


Now create a crosstab query like:
TRANSFORM Sum(Ally.Qty) AS SumOfQty
SELECT Ally.Month
FROM Ally
GROUP BY Ally.Month
PIVOT Ally.Field;
and this results in:
Month 1 2
1 15 22
2 8 7
Where the left 1 and 2 are the months and the top 1 and 2
are the Field#
I called the table Ally
Hope this helps.
Fons