Query with a calculated field that uses multiple records

[dfeim]

I am looking to create a query with the result being a sum of the number of
events that happen with a specified period. To clarify, the database table I
am working from has a column with a person_id and one with event_date. i
would like the query to sum (for the aggregate population) the number of
times a person has events that are within a specified period of each other (i.
e. 2 weeks or whatever you choose).

example:

person_id event_date
456def 09/05/2006
123abc 10/14/2006
456def 10/15/2006
789ghi 10/16/2006
123abc 10/17/2006
789ghi 10/19/2006

In this case I would like the result to = "2" because person 123abc had 2
events within 2 weeks of each other = which equals 1 count, 456def had 2
events but far apart = which equals 0 count, and 789ghi had 2 events within 2
weeks of each other = which equals 1 count. A person can have many counts
(theoretically max = 1 less than total events) and I can reorganize the
database to have the each person_id listed in order next to each of their
associated events if that helps with the query.

Does this make sense?
Thanks for any help given!!

 

[Smartin]

I am looking to create a query with the result being a sum of the number of
events that happen with a specified period. To clarify, the database table I
am working from has a column with a person_id and one with event_date. i
would like the query to sum (for the aggregate population) the number of
times a person has events that are within a specified period of each other (i.
e. 2 weeks or whatever you choose).

example:

person_id event_date
456def 09/05/2006
123abc 10/14/2006
456def 10/15/2006
789ghi 10/16/2006
123abc 10/17/2006
789ghi 10/19/2006

In this case I would like the result to = "2" because person 123abc had 2
events within 2 weeks of each other = which equals 1 count, 456def had 2
events but far apart = which equals 0 count, and 789ghi had 2 events within 2
weeks of each other = which equals 1 count. A person can have many counts
(theoretically max = 1 less than total events) and I can reorganize the
database to have the each person_id listed in order next to each of their
associated events if that helps with the query.

Does this make sense?
Thanks for any help given!!

Douglas Steele's response to my question in this thread might do the trick:

http://tinyurl.com/ybzcsq

You will of course have to adjust the table name and the condition in
the WHERE clause.