Report With Months In Columns

[Daveo]

Hi Everyone,

I have a table with the following fields:

ID (autonumber)
Employee (text)
Month (mmm-yy)
Basic Salary (Currency)
Hours (number)
Overtime (Currency)
Deductions (Currency).

Each employee has a record for each month.

I would like to create a report that looks like the following:

<<Employee>>
--------------------------------------------------------------------------------------------------------------------------------------
Jan Feb Mar Apr May Jun
Jul Aug Sep Oct Nov Dec
--------------------------------------------------------------------------------------------------------------------------------------
Basic Salary
Hours
Overtime
Deductions

I've tried for hours messing about with crosstab queries and the like
and am having no joy.

Can anyone please help?

Many thanks - David

 

[Guest]

If you want to use a crosstab, you must normalize your data first:

SELECT ID, Employee, [Month], "Basic Salary" as RowTitle, [Basic Salary] as
Amount
FROM tblNoNameGiven
UNION ALL
SELECT ID, Employee, [Month], "Hours", [Hours]
FROM tblNoNameGiven
UNION ALL
SELECT ID, Employee, [Month], "Overtime", [Overtime]
FROM tblNoNameGiven
UNION ALL
SELECT ID, Employee, [Month], "Deductions", [Deductions]
FROM tblNoNameGiven;

Then create a crosstab where Employee, ID, and RowTitle are Row Headings. A
calculation of [Month] is your column heading. Sum(Amount) is your value.

 

[Daveo]

Hi Duane,

Many thanks for getting back to me. I have done what you said and it
seems to be in the right direction. However, the crosstab query
returns results like this:

ID Employee RowTitle Jan Feb Mar............
-----------------------------------------------------------------------------------
1 David Basic Salary x
1 David Deductions x
1 David Hours x
1 David Overtime x
2 David Basic Salary y
2 David Deductions y
2 David Hours y
2 David Overtime y
3 David Basic Salary z
3 David Deductions z
3 David Hours z
3 David Overtime z

Is it possible to make it so everything appears in the same four rows?

Many thanks again,

David

 

[Guest]

It would help tremendously if you had included your SQL view. I expect you
only need to remove the ID field from the crosstab.

 

[Daveo]

Hi Duane,

I'll remember that for next time. You were spot on though - I removed
the ID field and it's perfect.

Thankyou very much for your help - you've saved me a lot of hassle and
I'll know for next time now!

Cheers - David