Hi All,
Another cross tab q problem. What I'm trying to do is count how many consultants are working on each project for that month. The query needs to show past months as well.
Each consultant has a [MobilisedDate] and a [DemobilisedDate]. These dates could be weeks apart or years apart. Therefore if consultant "A" [MobilisedDate]= 1.1.02 and his [DemobilisedDate]=1.4.02 how do I get him to be counted in months (jan,feb,mar,apr)
My cross tab query is working but the Column function is grouped on the [MobilisedDate] therefore only counts people who were actualy mobilised on that month. They are not included in the next months count:-( The Pivot looks like:
PIVOT Format([Mobilised Date],"mmm"
In ("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"
LOCATION PROJECT jan|feb|mar|apr|
------------------------------------------------
B AB 12 14
B vv 4 7
B TE 6 4
B KL 47 52
C AB 4 30
C TE 11 0
All help is much apreciated
Thanks ;-)
Another cross tab q problem. What I'm trying to do is count how many consultants are working on each project for that month. The query needs to show past months as well.
Each consultant has a [MobilisedDate] and a [DemobilisedDate]. These dates could be weeks apart or years apart. Therefore if consultant "A" [MobilisedDate]= 1.1.02 and his [DemobilisedDate]=1.4.02 how do I get him to be counted in months (jan,feb,mar,apr)
My cross tab query is working but the Column function is grouped on the [MobilisedDate] therefore only counts people who were actualy mobilised on that month. They are not included in the next months count:-( The Pivot looks like:
PIVOT Format([Mobilised Date],"mmm"
In ("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"LOCATION PROJECT jan|feb|mar|apr|
------------------------------------------------
B AB 12 14
B vv 4 7
B TE 6 4
B KL 47 52
C AB 4 30
C TE 11 0
All help is much apreciated
Thanks ;-)
