Results 1 to 4 of 4
  1. #1
    Sinoe is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2018
    Posts
    2

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

    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.

    Attached Thumbnails Attached Thumbnails Query and Partial results.PNG  

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,989
    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.

  3. #3
    Sinoe is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2018
    Posts
    2
    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.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,315
    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.

    Code:
    SELECT incident, protagonist, min(offenceDate) as Earliest
    FROM myTable
    GROUP BY incident, protagonist
    Q2.
    Code:
    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)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 11-05-2015, 02:07 PM
  2. Replies: 6
    Last Post: 11-25-2013, 10:53 AM
  3. Replies: 5
    Last Post: 10-11-2013, 07:29 AM
  4. Show Only One Occurrence of a Record
    By tsn.s in forum Queries
    Replies: 1
    Last Post: 04-23-2012, 09:29 AM
  5. How to show all months
    By Brian62 in forum Queries
    Replies: 4
    Last Post: 10-20-2009, 08:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums