Help in query required

[rammudali]

Hi all,

It is nice to be a part of this forum.

i have a small problem for which help is required.

I have a db and through which a query, a multi line, is generated where i have current and next assignments placed under each date(week) Colum. for look ahead plan and decision making process. Small sample is as follows

SELECT test.name, test.FROMDATE, test.TODATE, test.ASSIGN, IIf(#12/9/2010#>=[fromdate] And #12/9/2010#<=[TODATE],[ASSIGN],"") AS week1, IIf(#12/16/2010#>=[fromdate] And #12/16/2010#<=[todate],[ASSIGN],"") AS week2, IIf(#12/23/2010#>=[fromdate] And #12/23/2010#<=[todate],[ASSIGN],"") AS week3, IIf(#2/3/2011#>=[fromdate] And #2/3/2011#<=[todate],[ASSIGN],"") AS week4
FROM test
ORDER BY test.EMPNO, test.FROMDATE;

now i want to generate a query with a single row for each name showing the assignments(if "leave" then "leave" compulsorily or [ASSIGN]) under each week Colum. this is required for me for look ahead plan and decision making process. Specimen of desired output is as under
Name
Week1
Week2
Week3
Week4
Smith
icc
Icc
Leave
Leave
John
Leave
Leave
Ccic
icc