forcing a date into query where there is no data

[exceliz]

I have this sql which allows me to have 52 weeks populated whether there is any data or not

TRANSFORM Count(KG_LC_EnglishWL18wkPTLOutputQry.UnitNo) AS CountOfUnitNo
SELECT KG_LC_EnglishWL18wkPTLOutputQry.Specialty
FROM KG_LC_EnglishWL18wkPTLOutputQry
WHERE (((KG_LC_EnglishWL18wkPTLOutputQry.Specialty) In ("BREAST","COLOREC","ENT","GASTRO","GYNA","OPHT","ORAL","ORTH","PAIN","PLAS","SURG","UROL","VASC")) AND ((KG_LC_EnglishWL18wkPTLOutputQry.Planned)="Active") AND ((KG_LC_EnglishWL18wkPTLOutputQry.[Eng/Wel])="English") AND ((KG_LC_EnglishWL18wkPTLOutputQry.[P'wayLoc'])="IP"))
GROUP BY KG_LC_EnglishWL18wkPTLOutputQry.Specialty, KG_LC_EnglishWL18wkPTLOutputQry.[P'wayLoc']
PIVOT (IIf([RTTWeeks]>=52,52,[RTTWeeks])) In (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52);

i have something completely different i am doing but i want my dates populated just like the weeks were able to above. but i dont want it as a pivot (whether that is possible because i need columns for session start and finish times and actual start and finish times and utilisation and i need to plot graphs from this data.
so i need to enter a date into the dataset but with no columns populated as nothing happened that day?

Thanks for any help
liz x

 

[Norie]

What tables/fields will be used for this?

Also, how are you going to create the chart(s)?

Are you sure you actually need there blank records for that?

 

[exceliz]

thanks for the response....the data is about theatres, its coming from a query or i could make it a make table?

I have a date, day, theatre room, sessions start, sessions end, actual start, actual end, utilsation fields.

But weekends dont always have operations, and i am having to hightlight the columns manually each theatre, month, and session to produce the graph. The Graph shows the actual verses the planned and also on a seperate axis the utilisation % for that session.

Thanks

 

[Norie]

What tables is the query based on?

What I'm trying to establish is if there is some way we can add the missing dates in.

The easiest way to do that would be if one of the tables had a field in it that included all the dates of interest.

What is it you want to plot anyway and on what type of graph(s)?

You mention actual and planned, is that a separate line chart for each theatre with a series each for actual and planned.

 

[exceliz]

hi

i dont know how to explain so i have copied my sql for this table of data:

SELECT Theatre_Session_Op_Start_End_Elective_KPI.OpDay, Theatre_Session_Op_Start_End_Elective_KPI.OpDate, Theatre_Session_Op_Start_End_Elective_KPI.Theatre, Theatre_Session_Op_Start_End_Elective_KPI.SessionSurgeon, Theatre_Session_Op_Start_End_Elective_KPI.NewSessionStart, Theatre_Session_Op_Start_End_Elective_KPI.NewSessionEnd, Theatre_Session_Op_Start_End_Elective_KPI.SessionLength, Theatre_Session_Op_Start_End_Elective_KPI.StartTime, Theatre_Session_Op_Start_End_Elective_KPI.MaxOfIntoRecoveryTime, Format([MaxOfIntoRecoveryTime]-[StartTime],"Short Time") AS OperatingTime, Format([OperatingTime],"Fixed")/Format([SessionLength],"Fixed") AS Utilisation, Format([StartTime],"Fixed")-Format([NewSessionStart],"Fixed") AS [Over/Under Run], IIf([StartTime]<[NewSessionStart],"-" & Format([StartTime]-[NewSessionStart],"Short Time"),Format([StartTime]-[NewSessionStart],"Short Time")) AS Starts, IIf([MaxOfIntoRecoveryTime]<[NewSessionEnd],"-" & Format([MaxOfIntoRecoveryTime]-[NewSessionEnd],"Short Time"),Format([MaxOfIntoRecoveryTime]-[NewSessionEnd],"Short Time")) AS finishes
FROM Theatre_Session_Op_Start_End_Elective_KPI
WHERE (((Theatre_Session_Op_Start_End_Elective_KPI.Theatre) Like "D*") AND ((Theatre_Session_Op_Start_End_Elective_KPI.SessionSurgeon)<>"E" Or (Theatre_Session_Op_Start_End_Elective_KPI.SessionSurgeon) Is Null));

fields used (dont really use operating time yet)
Theatre OpDay OpDate SessionSurgeon NewSessionStart NewSessionEnd SessionLength StartTime MaxOfIntoRecoveryTime OperatingTime Utilisation

to plot the graph i need to plot each theatre seperately with the op date , day, session surgeon along the x axis
and i have the 5 series:
session start
session finish
actual start
actual finish
utilisation (on a secondary axis)

sorry im not very good at access
thanks for taking the time to look

Liz

 

[Norie]

Liz

Do you want to plot the length, both actual and planned, of every event in a theatre (operation/procedure?) by date?

Wouldn't it make more sense to group that data in some way?

For example by week as you appear to be doing in the first query?

Or am I totally off the mark.

I'll admit I don't actually know what some of the terms you are using mean exactly.

I can guess quite a few but the for some the terminology is eluding me.

eg what's a 'session'?

I think it's some sort of allocation/time slot and would be the 'planned' part.

Anywhere close or still way off?

PS You've still not mentioned where you are going to create the charts.

If it's in Excel you should probably jsut use Access to get the data into some sort of usable state rather than do all the calculations etc.

You can let Excel handle that.

 

[exceliz]

dont worry about that query quering that table its build from several update queries to give me this output in the first place - now i have the dataset i need to measure utilisation its too much of a task!

yeah im going to use excel but i dont get the same amount of rows for each theatre session. (session is planned) there are two time slots 9-12 and 2-5 or there abouts always the same though.

so i just want to measure the actual start against the session start and the acual finish against the session finish

but its proving too much work to do each month.
i am at the moment putting the data into excel and just sorting it on theatre opdate and session to then highlight each column to form the chart.... i have 3 theatres i am concentrating at the moment and if proved useful there are another 9! i have picked the easy ones too

i have no better way of doing this.

thanks for youe help anyways x

 

[Norie]

Liz

Not having the same amount of rows was one reason I suggested summarizing/grouping things.

I actually tried something last night - a very simple actual vs planned chart.

The data for actual was spread over the same time period as that for planned, but period was per month.

I tried just using the two as they were but that didn't work.

So I grouped the Actual by month, ending up with the same no of rows as the plannned.

With that I was able to easily make charts of all types.

That's a very simple example and is probably no way near what you have but it might be worth trying something like that.

Unless you need each data as a data point on the chart.

 

[exceliz]

thanks for all your help and time!
i will have a go at that, if that fails i may try plot a session a day somehow.

thanks again