Calculate time between events

  • Thread starter Thread starter dpicone
  • Start date Start date
D

dpicone

I have an Access (2003) data base that includes date of complaint and
location of complaint. I need to calculate the time between each complaint
to construct a g-chart. How do I do this calcuation? Thank you for your
help.
 
"How" depends on "what" ... please describe your data structure ("what") in
a bit more detail.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have set up a cross tab query that lists all of the dates for a two month
period and then for each location the number 1 is used to designate that a
complaint was received on that day or 0 if no complaint was received. For
each date and for each location I need to calculate a count or consecutive
days. For example the output for each location with the calculated field (
Time Between Event )would need to be:
Date "Unit A Complaint" "Time Between Event Days"
1/1/2009 0 0
1/2/2009 1 1
1/3/2009 1 2
1/4/2009 0 0
1/5/2009 1 1
1/6/2009 1 2
1/7/2009 1 3
1/8/2009 0 0

The "Time Between Event Days" is what I am trying to calcuate instead of
doing manually? Thanks, Deb
 
correlated subqueries





"How" depends on "what" ... please describe your data structure ("what") in
a bit more detail.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
If I'm understanding your description, you are describing what you do with
the data, not the underlying data itself.

More info, please.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have two fields of data, one is a date field that is the date of the
complaint (Date), the second is a text field of the location where the
complaint originated (Unit). That is what I started with. Thanks
 
So if you had 100 records, you'd need to end up with 99 numbers representing
the differences between successive complaints?

One way to approach this would be to create a function that steps through an
ordered (sorted) set, calculating (and storing) the difference between each
successive pair. By the way, you'd have to sort by that date/time field to
make sure you got "successive pairs".

Then, once you have the 99 calculated differences, you could do whatever you
need to with them.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
if you were using SQL Server, then you would be able to easily give us
the DDL to recreate these tables
 
Please ignore the sniping -- finding fault without offering alternatives
doesn't help much, does it?

If you are not familiar with VBA, you would have no way to tell if any
function/code you received was appropriate, whether your situation required
some modifications, nor be able to trouble-shoot it if/when it failed. I
can provide (see below) a generic set of steps, but you will need to find a
way to build, test, debug, and deploy VBA!

1. start with a sorted recordset (you could set a recordset in code to a
query, which could do the sorting)
2. start at the beginning (first date/time)
3. capture the date/time value
4. step to the next record -- quit if there are no more
5. capture the (next) date/time value
6. calculate the difference between the two date/time values
7. store the difference (you could create a table to hold these)
8. go back to #4

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
you're talking about looping through records, I assumed that Jet has
some sort of cursor functionality to do this (because they're removing
VBA, remember?)
 
and I _AM_ offering alternatives:

a) move to SQL Server
b) share your schema with people when you ask help (right click,
create to, new query window)
c) use a platform that supprots looping through and doing stuff. Jet
doesn't
 
Back
Top