How to format this leave detail data into tabular report format 1

[RaffiqEddy]

Hello, Expert!

I need help on how do I translate this data...

Table "LeaveDetail"

StaffNo | StartDate  | EndDate    | LeaveType |
1       | 01/11/2005 | 04/11/2005 | AL        |
2       | 02/11/2005 | 03/11/2005 | MC        |
3       | 04/11/2005 | 05/11/2005 | EL        |
1       | 10/11/2005 | 11/11/2005 | EL        |

Into this report format...

Month: November
StaffNo |01 |02 |03 |04 |05 |06 |07 |08 |09 |10 |11 |12....
  1     |AL |AL |AL |AL |   |               |EL |EL |
  2     |   |MC |MC |   |   |
  3     |   |   |   |EL |EL |

Using only query statement...

Is this possible??

TIA

Reagrds.

 

[dhookom]

Create a table of months tblMths with a single numeric field Mth. Add records for values 1-12.
Then create a query with this SQL:

TRANSFORM First(LeaveDetail.LeaveType) AS FirstOfLeaveType
SELECT LeaveDetail.StaffNo
FROM tblMths, LeaveDetail
WHERE (((tblMths.Mth) Between Month([StartDate]) And Month([EndDate])))
GROUP BY LeaveDetail.StaffNo
PIVOT tblMths.Mth In (1,2,3,4,5,6,7,8,9,10,11,12);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

Sorry, I didn't notice your dates are probably d/m/y. I trust you can adjust the sql and the records in the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RaffiqEddy]

OK I'll try...

 

[RaffiqEddy]

dhookom,

Here the result...

I've to change the column header to "Day" intead of "Month" and
I've create tblDay which contain from day 1 --> 31

So now the query look like this...

TRANSFORM First(nLeaveTypeNo) AS FirstOfLeaveType
SELECT LeaveDetail.nEmpNo
FROM tblDays, LeaveDetail
WHERE tblDays.Day Between Day(dStartDate) And Day(dEndDate) AND
      Month(dStartDate)=10 AND
      Year(dStartDate)=2005
GROUP BY LeaveDetail.nEmpNo
ORDER BY LeaveDetail.nEmpNo
PIVOT tblDays.Day In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

The problem, if the data having date look like this:

Table "LeaveDetail"

StaffNo | StartDate  | EndDate    | LeaveType |
1       | 03/10/2005 | 03/11/2005 | AL        |
2       | 02/10/2005 | 03/11/2005 | MC        |

which duration of leave is more than 1 month, but the result appear like this which is inacurate...

Month: October, 2005
StaffNo |01 |02 |03 |04 |05 |06 |07 |08 |09 |10 |11 |12| 13... <-- day
  1     |   |   |AL |
  2     |   |MC |MC |

It should appear like this...

Month: October, 2005
StaffNo |01 |02 |03 |04 |05 |06 |07 |08 |09 |10 |11 |12| 13... <-- day
  1     |   |   |AL |AL |AL |AL |AL |AL |AL |AL |AL |AL |AL...
  2     |   |MC |MC |MC |MC |MC |MC |MC |MC |MC |MC |MC |MC...

Can u guide me again on this??

TIA

Regards.

 

[dhookom]

Try this SQL. I just noticed you were inconsistent with your column names from your SQL and your sample data. Perhaps you are using the ugly Caption property of fields in table design. I assume you can sort this out.

PARAMETERS [Enter Month] Short;
TRANSFORM First(LeaveDetail.LeaveType) AS FirstOfLeaveType
SELECT LeaveDetail.nEmpNo
FROM tblDays, LeaveDetail
WHERE (((DateSerial(Year(Date()),[Enter Month],[Day])) Between [dStartDate] And [dEndDate]))
GROUP BY LeaveDetail.nEmpNo
PIVOT tblDays.Day In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RaffiqEddy]

Thanks dhookom,

It Works!

Hope I can contribute more STAR for you for the excellent guide..

BTW, sorry for the inconsistency

Take care,

Regards.