Results 1 to 2 of 2
  1. #1
    Daddeo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    1

    Query to Find repeat instances in a field

    I have a table that contains customer ticket data and need to create a query to identify how often customers have a repeat ticket within 3 days. I am currently using Design Query with 2 instances of the the table and joining by customer ID.
    Table Fields:
    Report_TS


    Customer_ID
    Repair_Center
    Repair_Type
    Ticket_Num
    Tech_ID

    Example data
    Report_TS Customer_ID Repair_Center Repair_Type Ticket_Num Tech_ID
    1/1/16 1300 Bob123 Uptown GQ UGQ123 444
    1/2/16 1000 Bob123 Uptown GQ UGQ130 455
    1/2/16 1100 Cal333 Main ET MET132 333
    1/3/16 0900 Bob123 Uptown GQ UGQ135 444
    1/3/16 0930 Ann321 Downtown F1 DF1055 111
    1/4/16 1100 Cal333 Main ET MET135 322










    Expected Output
    Month Repair_Center Ticket_Num Tech_ID RptTkt_Num
    Jan Uptown UGQ123 444 UGQ130
    Jan Uptown UGQ130 455 UGQ135
    Jan Uptown UGQ135 444
    Jan Main MET132 333 MET135
    Jan Main MET135 322
    Jan Downtown DF1055 111










    Any assistance on best way to proceed is greatly appreciated! I am getting duplicates

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something like

    Code:
    SELECT Month(ReportTD) as Mth, RepairCentre, TicketNum, TechID, (SELECT top 1 ticketnum from myTable as T where customerID=myTable.customerID and ReportTD>myTable.ReportTD and ReportTD<=myTable.ReportTD+3 ORDER BY ReportTD) AS RptTicketNum
    From MyTable

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

Similar Threads

  1. Search query not returning all instances
    By oneillp111 in forum Access
    Replies: 1
    Last Post: 07-31-2014, 07:51 AM
  2. Replies: 5
    Last Post: 11-08-2013, 06:25 AM
  3. Replies: 4
    Last Post: 10-30-2012, 06:32 PM
  4. Replies: 4
    Last Post: 09-18-2012, 03:01 PM
  5. Replies: 30
    Last Post: 08-15-2012, 02:25 PM

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