Column and row headings

[tezread]

Hi there!

I have a query which outputs Numberofworkdays as rows. The Numberofworkdays could be between 0-12 but at the moment if there isn't a value between that, then that isn't reflected in the cross tab query.
I need to have the column headings specified as something else (hospital) so i cannot use them. Is there a way to specify rows so they are presented in the results as a '0' even though technically there isn't a value associated with it.

I have a sneeky feeling this will involve me using the criteria property and the Nz function

see attached for illustration

Current query is

TRANSFORM Nz(Count(qry_cardiology1.EpisodeID),0) AS CountOfEpisodeID
SELECT qry_cardiology1.NumberOfWorkDays2
FROM qry_cardiology1
WHERE (((qry_cardiology1.RequestDatetime) Between #11/1/2010# And #11/30/2010#))
GROUP BY qry_cardiology1.NumberOfWorkDays2
PIVOT qry_cardiology1.ReferringHospital In ("Dudley","Walsall","Wolverhampton");

 

[vbaInet]

Nothing to do with Nz(), it has to do with your joins.

In the qry_cardiology1 query, you need to change the Inner Join to Left or Right join to pull all records that contain all NumberOfWorkDays2.

 

[tezread]

Hi

I don't have an inner join in the query qry_cardiology1

SELECT tbl_Data.AdmissionDate, tbl_Data.DischargeDatetime, tbl_Data.EpisodeID, tbl_Data.Status, tbl_Data.Outcome, tbl_Data.ReferringHospital, tbl_Data.ReceiveHospital, tbl_Data.DaysAdmitToTransfer, tbl_Data.DaysRequestToTransfer, Int(IIf(tbl_Data!DaysRequestToProcedure1<0,"0",IIf(tbl_Data!DaysRequestToProcedure1>=12,"12",tbl_Data!DaysRequestToProcedure1))) AS Days, tbl_Data.DaysRequestToProcedure1, tbl_Data.DaysTransferToProcedure1, tbl_Data.DaysTransferToProcedure2, tbl_Data.DaysTransferToProcedureN, tbl_Data.DaysAdmitToDischarge, tbl_Data.RequestProcedureType, tbl_Data.Procedure1, tbl_Data.Procedure2, tbl_Data.ProcedureN, tbl_Data.RequestDatetime, tbl_Data.Procedure1DateTime, Int([Procedure1Datetime]-[RequestDatetime]-(DateDiff("ww",[RequestDatetime],[Procedure1Datetime],7)-(Weekday([RequestDatetime])=7))-(DateDiff("ww",[RequestDatetime],[Procedure1Datetime],1)-(Weekday([RequestDatetime])=1))-(Select Count(*) from [tblHolidays] where [HolidayDate] between [RequestDatetime] and [Procedure1Datetime]))+1 AS NumberOfWorkDays, Int(IIf([NumberofWorkDays]<0,"0",IIf([NumberofWorkDays]>=12,"12",[NumberofWorkDays]))) AS NumberOfWorkDays2
FROM qry_surgery RIGHT JOIN (qry_NoProcedure RIGHT JOIN tbl_Data ON qry_NoProcedure.EpisodeID = tbl_Data.EpisodeID) ON qry_surgery.EpisodeID = tbl_Data.EpisodeID
WHERE (((tbl_Data.Outcome)="Completed") AND ((qry_surgery.EpisodeID) Is Null) AND ((qry_NoProcedure.EpisodeID) Is Null));

 

[vbaInet]

I see this is a calculated field

Int(IIf([NumberofWorkDays]<0,"0",IIf([NumberofWorkDays]>=12,"12",[NumberofWorkDays]))) AS NumberOfWorkDays2

Do you see 6, 10 and 11 based in this calculated field?

WHERE (((tbl_Data.Outcome)="Completed") AND ((qry_surgery.EpisodeID) Is Null) AND ((qry_NoProcedure.EpisodeID) Is Null))

With this criteria in place, does it pull in the NumberOfWorkDays2 6, 10 and 11 records?

 

[tezread]

I see this is a calculated field

Int(IIf([NumberofWorkDays]<0,"0",IIf([NumberofWorkDays]>=12,"12",[NumberofWorkDays]))) AS NumberOfWorkDays2

Do you see 6, 10 and 11 based in this calculated field?

WHERE (((tbl_Data.Outcome)="Completed") AND ((qry_surgery.EpisodeID) Is Null) AND ((qry_NoProcedure.EpisodeID) Is Null))

With this criteria in place, does it pull in the NumberOfWorkDays2 6, 10 and 11 records?

No this doesn't pull out the days 6, 10 ad 11

 

[vbaInet]

Then therein lies your problem.

You will somehow need to populate to get the missing working days in there.

Create a table with the numbers 1 to 12, include it in your query and get it to pull the workdays from that table.

 

[tezread]

Then therein lies your problem.

You will somehow need to populate to get the missing working days in there.

Create a table with the numbers 1 to 12, include it in your query and get it to pull the workdays from that table.

Hi - haven't worked on this for a while but have come back onto it today.
I am struggling a bit to be honest understanding how to take this forward

 

[vbaInet]

Like already mentioned, you need to create a table with one Number field with the numbers 1 to 12 as records. JOIN this table to your number of Workdays field, but change the join so that you have ALL records from the new table.

 

[tezread]

Like already mentioned, you need to create a table with one Number field with the numbers 1 to 12 as records. JOIN this table to your number of Workdays field, but change the join so that you have ALL records from the new table.

Thankyou vbaInet- I created as table called tblDays which has number values of 0-12. The bit where I am getting confused is that the field Numberofworkdays is in a query - not a table. I am not sure how to do joins between a table and a query

 

[vbaInet]

Double click the field and drag it to the other field in the other query and it will create the join. Right click the join and change it to include ALL from the table.

 

[tezread]

I am royally stumped lol - sorry vbaInet

 

[vbaInet]

Attach your db and I'll show you how.

 

[tezread]

Hi

Please see attached

Queries in question here are:

'qry_cardiology1' which is generating numberofworkdays

and the cross tab query IHT_network_query which is what I am using to view my results and populate the report.

As you may notice - this is a reporting tool. The data is pulled from an web based system into CSV format and a query generates the table tbl_data. I cannot modify the tbl_data table.
This has had an impact on normalisation - and I am sure there is scope for improvement

many thanks

tez

 

[vbaInet]

See attached.

 

[tezread]

Thank you for that vbainet.

It works great but a soon as I put a where clause on the cross tab query - in this case where requestdatetime is between and xXXX and XXXX - I get the old results where it only shows workdays 1-7 - or whatever days apply to that date range.

 

[vbaInet]

Yes, because those are the records the criteria match.

 

[tezread]

Yes, because those are the records the criteria match.

Thought so - but is there a way to add the workdays that have '0' for each of the referring hospitals

 

[vbaInet]

You could add a Is Null criteria in the OR part.

 

[tezread]

You could add a Is Null criteria in the OR part.

Already tried that - sorry - you must be thinking what an muppet he is

 

[vbaInet]

Working as expected then?