query to show repeated instance of the same incident within 6 months of the initial occurrence

[Sinoe]

Hello Access Superstars,

Please help me develop a query to return a repeated instance of the same incident that happened within 6 month of the initial occurrence. For example if some one was abused sexually in January and then again in July, how do I structure a query to return the repeated incidence. A sample record and the initial query is included in this post.

I have attached the query to that returns the initial results.

[ranman256]

can you just run a Count query : personID ,Description , Count(Description)
show items >1.

but for the time elapsed,
make a make table query, sort on person events, EventDate
write to table, with blank ElapsedTime field

then use vb to loop thru the table, it calculates the elapsed time between events. Elapsed= DateDiff("d",Date1,Date2)
then you can query on this table for the ones in your range.

[Sinoe]

Thanks for your response, Ranman256. I do not know VBA so did not try to use the solution. Is there another way to do this without VBA. I tried using a self-join but still could not get the repeat occurrence.

Thanks again for your response.

[CJ_London]

think you can do this in a query. I don't have the time to write out from your image so this is a guide - for the future, recommend you post the sql text and surround with the code tags.

1. create a query grouping by incident (and protagonist? not clear from your description) and min offence date
2. join this query to your main table on incident (and protagonist?) and set the criteria for the main table offence date to be between the first query offence date and first query offence date plus 6 months

e.g. something like

Q1.

SELECT incident, protagonist, min(offenceDate) as Earliest
FROM myTable
GROUP BY incident, protagonist

Q2.

SELECT myTable.* 
FROM myTable INNER JOIN Q1 on myTable.incident=Q1.incident AND myTable.protagonist=Q1.protagonist
WHERE myTable.offenceDate BETWEEN Q1.Earliest AND dateadd("m",6,Q1.Earliest)