Column and row headings

tezread

Registered User.
Local time
Today, 13:18
Joined
Jan 26, 2010
Messages
330
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");
 

Attachments

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.
 
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));
 
I see this is a calculated field
Code:
Int(IIf([NumberofWorkDays]<0,"0",IIf([NumberofWorkDays]>=12,"12",[NumberofWorkDays]))) AS NumberOfWorkDays2
Do you see 6, 10 and 11 based in this calculated field?

Code:
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?
 
I see this is a calculated field
Code:
Int(IIf([NumberofWorkDays]<0,"0",IIf([NumberofWorkDays]>=12,"12",[NumberofWorkDays]))) AS NumberOfWorkDays2
Do you see 6, 10 and 11 based in this calculated field?

Code:
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
 
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.
 
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
 
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.
 
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
 
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.
 
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
 

Attachments

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.
 

Users who are viewing this thread

Back
Top Bottom