view all dates between two dates parameters in the crosstab query headings

[Projectnow]

hi,

I need to view all dates between two dates parameters in the crosstab query headings
where I use this syntax

PARAMETERS d DateTime, b DateTime;
TRANSFORM Sum(Vaclist.Period) AS S
SELECT Vaclist.EmpID
FROM Vaclist
WHERE (((Vaclist.VacDate) Between [d] And [b]))
GROUP BY Vaclist.EmpID
PIVOT Vaclist.VacDate;

and it doesn't seem to work that way

any suggestions would be really appreciate

thank you in advance
projectnow

[Rabbit]

You'll have to explain what you mean by it doesn't work because it works fine for me.

[Projectnow]

Hi, Rabbit
thank you for your reply
I mean that all the dates between two dates parameters won't be a query headings
just the date which has a value will be viewed but not all the dates
I need it to be like that

                5/1/2000    5/2/2000    5/3/2000    5/4/2000    5/5/2000
Name            8
Name                                                    8
Name                                2
Name
8

thanks again for your reply

[Rabbit]

I know what you're looking for. I don't know what you mean by not working. Because it worked for me. What is your table structure?

[Projectnow]

you do get all the dates between two dates even if any of the dates in the range has a null value?

how come the sql I posted doesn't work for that

[Rabbit]

Oh ok, I misunderstood, to get what you want, you will need to outer join a date table with all the dates.

[Projectnow]

Ok, I did as you instructed me, but I get the first record in the crosstab with null values, in other words, the first record is blank, is there something I am doing wrong with that?
I ended with this lines:

PARAMETERS d DateTime, b DateTime;
TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod
SELECT Vaclist.EmpID, Vaclist.Vactype
FROM qryEmployeeWorkDates LEFT JOIN Vaclist ON (qryEmployeeWorkDates.EmpID = Vaclist.EmpID) AND (qryEmployeeWorkDates.DateOfPeriod = Vaclist.VacDate)
WHERE (((qryEmployeeWorkDates.DateOfPeriod) Between [d] And [b]))
GROUP BY Vaclist.EmpID, Vaclist.Vactype
PIVOT qryEmployeeWorkDates.DateOfPeriod;

I really appreciate your insight

[Rabbit]

That's to be expected, you can filter those out in the where clause by excluding those that have a null in vaclist.empid.

[Projectnow]

Hi,

there's no null empid in query vaclist
I just needed to change from left join to inner join between qryEmployeeWork Dates and vaclist

glad to get help from the experts like you Rabbit

thank you again and over again

[Rabbit]

There's no nulls in the table, but there could be nulls as a result of an outer join. If you use an inner join, doesn't that just take you back to where you were? Aren't you now missing those other dates where there's no vacation logged?

[Projectnow]

Oh, yes that's true that just take me back to where I was,
now I am missing the dates where there's no vacation
but there could be nulls as a result of an outer join.
I can't see any nulls in query vaclist and qryemployeework dates
how can I trap that?

thank you very much

[Rabbit]

Try adding that where clause I mentioned earlier.

[Projectnow]

when I use is not null as in the sql

PARAMETERS d DateTime, b DateTime;
TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod
SELECT Vaclist.EmpID, tbl_VacType.Vactype
FROM (qryEmployeeWorkDates LEFT JOIN Vaclist ON (qryEmployeeWorkDates.DateOfPeriod = Vaclist.VacDate) AND (qryEmployeeWorkDates.EmpID = Vaclist.EmpID)) LEFT JOIN tbl_VacType ON Vaclist.VacTypeID = tbl_VacType.VacTypeID
WHERE (((Vaclist.EmpID) Is Not Null) AND ((qryEmployeeWorkDates.DateOfPeriod) Between #10/2/2012# And #10/12/2012#))
GROUP BY Vaclist.EmpID, tbl_VacType.Vactype
PIVOT qryEmployeeWorkDates.DateOfPeriod;

that's also get me back to the result from using an inner join

[Rabbit]

You'll probably have to subquery that query and filter out that row after the crosstab has already returned the data.

[Projectnow]

I really don't know how to filter out that row after the crosstab has already returned the data
I really need to accomplish this but there is something I can't understand with that query

thank you very much