How to show the data in such layout

[accesser2003]

I have this very important question, I dont know how to do it but I am sure Access support this and it is doable.

I have the following recorset:

Name LeaveType LeaveDate LeaveYear

Frank Annual 25/06/2007 2007
Frank Annual 30/04/2007 2007
Frank Unpaid 11/09/2004 2004

James Unpaid 04/03/2006 2006
James Unpaid 19/09/2006 2006

Meco Paternity 17/12/2004 2004
Meco Paternity 18/12/2004 2004
Meco Annual 02/07/2006 2006

Paolo Annual 25/06/2007 2007
Paolo Annual 12/06/2007 2007
Paolo Annual 24/06/2004 2004

I want the data to be shown in a report as follow:

Name: Frank

Leave Type 2007 2006 2004

Annual 2 0 0
Unpaid 0 0 1
Paternity 0 0 0


Name: James

Leave Type 2007 2006 2004

Annual 0 0 0
Unpaid 0 2 0
Paternity 0 0 0


Name: Meco

Leave Type 2007 2006 2004

Annual 0 1 0
Unpaid 0 0 0
Paternity 0 0 2

Name: Paolo

Leave Type 2007 2006 2004

Annual 2 0 1
Unpaid 0 0 0
Paternity 0 0 0


I WILL HIGHLY APPRECIATE IF SOME ONE HELP ME DO THIS VERY IMPORTANT TASK.

REGARDS,

 

[Rich]

A crosstab query should do it, but do you have both leave dates and leave year as separate dates in your table, if so why?

 

[accesser2003]

Dear Mr. Rich,

I will highly appreciate if you give me further details as I need it very very important. Just Please note that the LeaveDate and LeaveYear are in separate fields in the recordset LeavesStatistics.

Please help me as much as you can as soon as possible.

 

[accesser2003]

I have them in separate fields. the field Leave year is a result of the formula year(LeaveDate). This is because I need to show the Leave year in the report not the leavedate. Is this enough?