Gantt-type gaps in data

[annemu]

Hi all,
I hardly know how to explain what I am after, but I will try.
I am looking at data that shows when a process starts and when it ends. This relates to ICU patients who have particular types of IV lines inserted. We calculate their overall line days, ignoring the overlaps when they may have 2 lines in at the same time. Sometimes there are gaps between one line ending and another one starting (line free days). I can calculate total line days using a query, but if there are line free days, I want to be able to calculate these gaps (per patient)and subtract them from the overall line time(per patient). Does anyone get what I am trying to say? Visually I can use a Gantt chart in excel, but we are talking thousands of patients-worth of data, so it becomes a little unwieldy. I thought I could use an Access query to flag when there are gaps in the data, then at least I could calculate those few by hand.
Any ideas?

 

[Uncle Gizmo]

Can you provide a sample of the data?

 

[annemu]

Hi Gizmo,
See attached excel file with anonymised data. There are some patients who have a few different types of lines with overlap, but there is at least one in there who has a gap (line-free days)
Cheers
Anne

 

[gemma-the-husky]

depends how good you are with coding

i think this logic will work:

first sort the query output in order of start time

then you need to iterate all the rows, (ie in order of start time) so that as you do it, you store the latest end time you have found. if the next items start time is AFTER the latest end time, then you have identified a gap that can reduce the overall project time.

easy to test, once you have the code written


something like this might get close - you need to tidy this up

dim latestend as date

'intialise the latest end time
latestend = 0

set rst=dbs.openrecordset("ganttquery")
while not rst.eof

[COLOR="Red"] 'heres the logic - if the next starttime is after the latest end time found so far, there is a gap[/COLOR]
 if rst!starttime>latestend then
   msgbox("Here is a gap - What do you want to do with it?")
 end if
[COLOR="red"]'now see if the end time has changed[/COLOR]
 if rst!endtime>latestend then
  latestend = rst!endtime
 end if
 rst.movenext 
wend

 

[annemu]

Thanks Gemma,
I do understand your logic, as it is exactly the same logic I was trying to apply with a query calculation. I would not be the greatest in the world at coding - I understand the bare-bones of coding - self taught beginner!
Do you mean that I should use this code within a form event or can I use this within a query/macro?

 

[gemma-the-husky]

i assume you have a query which generates an output similar to the excel spreadsheet,

make sure this is sorted in order of starttime

then on a form, have a button which calls a procedure to process that query using the code example above. it will need a procedure name, and some variable declarations. You also need to decide how and what you do with any gaps it finds - hence my msgbox

 

[annemu]

Thanks very much - will give it a try - I have already configured a procedure based on the code but was unsure how to run it - cheers - Anne