Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Mar 2004
    Location
    Sydney Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access - SQL Getting duplicates

    I am workng with an Access app and have run into some SQL behaviour I normally sort out using VBS in my ASP page.......I can not seem to get this issue sorted:

    TBL_Support_Call is the main tble. For each record in this tble they can be multiple records in the TBL_Call_Staus_History table. EG if a call gets opened a records gets written containing the Status and the date. Then the records status may change four times therefore four records will be written the history table. How can I ensure the query only returns one TBL_Support_Call record no matter how many entries it has in the TBL_Call_Staus_History table?

    Here is the working query which insists giving me duplicates according to the number of records in the history table


    SELECT DISTINCT Format([DateTime],"dd/mm/yyyy") AS [Date Time], TBL_Support_Call.Ref, TBL_Support_Call.[Issue Summary],
    TBL_Support_Call.Comments, TBL_FollowUp.Comments AS [Follow up Comments], TBL_Support_Call.[User Name],
    TBL_Support_Call.BU, TBL_Support_Call.Issue, TBL_Support_Call.Link, TBL_Support_Call.Category,
    TBL_Support_Call.[Functional Area], TBL_Support_Call.[Contact Source], TBL_Support_Call.Status,
    Format([TBL_Call_Staus_History.CloseDate],"dd/mm/yyyy") AS [Closed Date], TBL_Support_Call.[Logged BY],
    TBL_Support_Call.Owner FROM (TBL_Support_Call
    LEFT JOIN TBL_FollowUp ON TBL_Support_Call.Ref = TBL_FollowUp.[Original Call Ref])
    LEFT JOIN TBL_Call_Staus_History ON TBL_Support_Call.Ref = TBL_Call_Staus_History.refID
    ORDER BY TBL_Support_Call.Ref DESC;

    TYIA

    Further to my post if I remove:

    Format([TBL_Call_Staus_History.CloseDate],"dd/mm/yyyy") AS [Closed Date], TBL_Support_Call.[Logged BY],

    from the query my duplicate issue goes away. Can a better join or better SQL overcome this?
    wind is your friend

  • #2
    New Coder
    Join Date
    Mar 2004
    Location
    Sydney Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT Format([DateTime],"dd/mm/yyyy") AS [Date Time]
    , TBL_Support_Call.Ref
    , TBL_Support_Call.[Issue Summary]
    , TBL_Support_Call.Comments
    , TBL_FollowUp.Comments AS [Follow up Comments]
    , TBL_Support_Call.[User Name]
    , TBL_Support_Call.BU
    , TBL_Support_Call.Issue
    , TBL_Support_Call.Link
    , TBL_Support_Call.Category
    , TBL_Support_Call.[Functional Area]
    , TBL_Support_Call.[Contact Source]
    , TBL_Support_Call.Status
    , Format([mmmm.maxdate],"dd/mm/yyyy") AS [Closed Date]
    , TBL_Support_Call.[Logged BY]
    , TBL_Support_Call.Owner
    , datediff("d",[DateTime],[mmmm.maxdate]) AS [Days Opened]
    FROM (
    TBL_Support_Call
    LEFT OUTER
    JOIN TBL_FollowUp
    ON TBL_FollowUp.[Original Call Ref] = TBL_Support_Call.Ref
    )
    LEFT OUTER
    JOIN ( SELECT refID
    , MAX(CloseDate) AS maxdate
    FROM TBL_Call_Staus_History
    GROUP
    BY refID ) AS mmmm
    ON mmmm.refID = TBL_Support_Call.Ref
    ORDER
    BY TBL_Support_Call.Ref DESC;
    wind is your friend


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •