Results 1 to 5 of 5
  1. #1
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232

    Duplicated Records Showing

    Hi I have a query that I pull from three tables. but it is showing duplicated records. The customer might have more than one workorder and this is causing my query to show duplicated records for customers.

    here is the code

    SELECT Calls.[Customer ID], Calls.[Customers Response], Customers.FirstName, Customers.LastName, Customers.[Home Number], Customers.[Cell Number], Workorders.[Service Call Date], Customers.[New Owner], Customers.[New Owner Letter Sent]
    FROM Workorders INNER JOIN (Customers INNER JOIN Calls ON Customers.CustomerID = Calls.[Customer ID]) ON (Customers.CustomerID = Workorders.[Customer ID]) AND (Workorders.[Customer ID] = Calls.[Customer ID])
    WHERE (((Workorders.[Service Call Date])<#1/1/2015#));



    I want to have only one customer records to show if the workorders service call date is <1/1/2015.

    Thanks Angie

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Use SELECT DISTINCT

    if you still get duplicates, you need to remove the columns causing it.

  3. #3
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232
    [QUOTE=Ajax;325278]Use SELECT DISTINCT

    if you still get duplicates, you need to remove the columns causing it.[/QUOTE

    This did not work.. I need to use that column so that I can just show the customer we have not been at their home in the last year.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Try changing your query to a Totals query. For the service call date, instead of using "Group By" in the Total row, select Max from the dropdown. This will give you the latest (most recent) call date that still meets the criteria.

  5. #5
    angie is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    232
    Thanks John G That worked great... Sorry it took so long to try but was out of town.. Have a great day

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

Similar Threads

  1. Replies: 2
    Last Post: 05-24-2016, 08:01 AM
  2. How to delete the duplicated records in a field?
    By jamal numan in forum Access
    Replies: 7
    Last Post: 10-28-2011, 01:39 PM
  3. Replies: 5
    Last Post: 10-27-2011, 10:25 AM
  4. Records duplicated based on field
    By dskulman in forum Queries
    Replies: 3
    Last Post: 03-03-2011, 06:26 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