Column and row headings

[tezread]

Working as expected then?

no - having 'Or is null' under requestdatetime where clause still gives me workdays listed from 1-7.

I understand that the criteria is only looking for what I am asking for but I don;t get the bit where I want it to provide the results as listed in my original excel attachment

 

[vbaInet]

If you put Or Is Null it will show you 1-7 and 11.

The other numbers do not fall within the criteria and will not be included.

 

[tezread]

If you put Or Is Null it will show you 1-7 and 11.

The other numbers do not fall within the criteria and will not be included.

I know - but the output needs to show the workdays that have '0' under all three referring hospitals as well- for 8,9,10 work days this is mind boggling. I think I am approaching this all wrong

Maybe i could have a condition in the countofepisode field that says if result is 0 then add this to the query as well?

 

[tezread]

aha - I have found a way around this by changing the cross tab query:

TRANSFORM Nz(Count(qry_cardiology1.EpisodeID),0) AS CountOfEpisodeID
SELECT qry_cardiology1.ReferringHospital
FROM qry_cardiology1
WHERE (((qry_cardiology1.ReferringHospital)="Dudley" Or (qry_cardiology1.ReferringHospital)="Walsall" Or (qry_cardiology1.ReferringHospital)="Wolverhampton") AND ((qry_cardiology1.RequestDatetime) Between #1/1/2011# And #1/31/2011#))
GROUP BY qry_cardiology1.ReferringHospital
PIVOT qry_cardiology1.NumberOfWorkDays2 In (0,1,2,3,4,5,6,7,8,9,10,11,12);

I do not have referring hospital as row heading though which is mildly irritating