By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,240 Members | 791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,240 IT Pros & Developers. It's quick & easy.

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

P: 13
hi,

I need to view all dates between two dates parameters in the crosstab query headings
where I use this syntax
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS d DateTime, b DateTime;
  2. TRANSFORM Sum(Vaclist.Period) AS S
  3. SELECT Vaclist.EmpID
  4. FROM Vaclist
  5. WHERE (((Vaclist.VacDate) Between [d] And [b]))
  6. GROUP BY Vaclist.EmpID
  7. PIVOT Vaclist.VacDate;
and it doesn't seem to work that way

any suggestions would be really appreciate

thank you in advance
projectnow
Oct 9 '12 #1

✓ answered by Rabbit

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS d DateTime, b DateTime; 
  2. SELECT * FROM (
  3.    TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod 
  4.    SELECT Vaclist.EmpID, Vaclist.Vactype 
  5.    FROM qryEmployeeWorkDates LEFT JOIN Vaclist ON (qryEmployeeWorkDates.EmpID = Vaclist.EmpID) AND (qryEmployeeWorkDates.DateOfPeriod = Vaclist.VacDate) 
  6.    WHERE (((qryEmployeeWorkDates.DateOfPeriod) Between [d] And [b])) 
  7.    GROUP BY Vaclist.EmpID, Vaclist.Vactype 
  8.    PIVOT qryEmployeeWorkDates.DateOfPeriod
  9. ) t
  10. WHERE EmpID IS NOT NULL;

Share this Question
Share on Google+
16 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You'll have to explain what you mean by it doesn't work because it works fine for me.
Oct 9 '12 #2

P: 13
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
Expand|Select|Wrap|Line Numbers
  1.                 5/1/2000    5/2/2000    5/3/2000    5/4/2000    5/5/2000
  2. Name            8
  3. Name                                                    8
  4. Name                                2
  5. Name
  6. 8
thanks again for your reply
Oct 9 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
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?
Oct 10 '12 #4

P: 13
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
Oct 10 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
Oh ok, I misunderstood, to get what you want, you will need to outer join a date table with all the dates.
Oct 10 '12 #6

P: 13
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:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS d DateTime, b DateTime;
  2. TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod
  3. SELECT Vaclist.EmpID, Vaclist.Vactype
  4. FROM qryEmployeeWorkDates LEFT JOIN Vaclist ON (qryEmployeeWorkDates.EmpID = Vaclist.EmpID) AND (qryEmployeeWorkDates.DateOfPeriod = Vaclist.VacDate)
  5. WHERE (((qryEmployeeWorkDates.DateOfPeriod) Between [d] And [b]))
  6. GROUP BY Vaclist.EmpID, Vaclist.Vactype
  7. PIVOT qryEmployeeWorkDates.DateOfPeriod;
  8.  
I really appreciate your insight
Oct 10 '12 #7

Rabbit
Expert Mod 10K+
P: 12,366
That's to be expected, you can filter those out in the where clause by excluding those that have a null in vaclist.empid.
Oct 10 '12 #8

P: 13
Hi,

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

glad to get help from the experts like you Rabbit

thank you again and over again
Oct 10 '12 #9

Rabbit
Expert Mod 10K+
P: 12,366
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?
Oct 10 '12 #10

P: 13
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 qryemployeeworkdates
how can I trap that?

thank you very much
Attached Images
File Type: jpg CROSSTAB.jpg (13.5 KB, 212 views)
Oct 10 '12 #11

Rabbit
Expert Mod 10K+
P: 12,366
Try adding that where clause I mentioned earlier.
Oct 10 '12 #12

P: 13
when I use is not null as in the sql
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS d DateTime, b DateTime;
  2. TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod
  3. SELECT Vaclist.EmpID, tbl_VacType.Vactype
  4. 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
  5. WHERE (((Vaclist.EmpID) Is Not Null) AND ((qryEmployeeWorkDates.DateOfPeriod) Between #10/2/2012# And #10/12/2012#))
  6. GROUP BY Vaclist.EmpID, tbl_VacType.Vactype
  7. PIVOT qryEmployeeWorkDates.DateOfPeriod;
  8.  
that's also get me back to the result from using an inner join
Oct 10 '12 #13

Rabbit
Expert Mod 10K+
P: 12,366
You'll probably have to subquery that query and filter out that row after the crosstab has already returned the data.
Oct 10 '12 #14

P: 13
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
Oct 10 '12 #15

Rabbit
Expert Mod 10K+
P: 12,366
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS d DateTime, b DateTime; 
  2. SELECT * FROM (
  3.    TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod 
  4.    SELECT Vaclist.EmpID, Vaclist.Vactype 
  5.    FROM qryEmployeeWorkDates LEFT JOIN Vaclist ON (qryEmployeeWorkDates.EmpID = Vaclist.EmpID) AND (qryEmployeeWorkDates.DateOfPeriod = Vaclist.VacDate) 
  6.    WHERE (((qryEmployeeWorkDates.DateOfPeriod) Between [d] And [b])) 
  7.    GROUP BY Vaclist.EmpID, Vaclist.Vactype 
  8.    PIVOT qryEmployeeWorkDates.DateOfPeriod
  9. ) t
  10. WHERE EmpID IS NOT NULL;
Oct 10 '12 #16

P: 13
Rabbit, Great!
you are genius!
thank you for your assistance and your patience
I ended with this
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS d DateTime, b DateTime;
  2. SELECT *
  3. FROM qrycrosstab
  4. WHERE (((qrycrosstab.EmpID) Is Not Null));
I really appreciate your help
thank you again and over again
Oct 10 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.