Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Dec 2008
    Posts
    25

    Unanswered: 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me ask you two questions in return

    1. if a TBL_Support_Call row has multiple TBL_Call_Staus_History rows, which of them would you like to use in the query?

    2. can a TBL_Support_Call row have multiple TBL_FollowUp rows, and if so, which of them would you like to use in the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    25
    Thank you for your time...

    1.. I would like to get the MAX date from TBL_Call_Staus_History I am tring to write that now but have discovered I need to have all the select elemsnts in a group by clause. Does this souns correct?

    2.. No the follow up comments get appended. this is a one to one relationship


    This is the first time I have had to code with ACCESS as a front end. I am an ASP guy who is probably a bit lazy with SQL. usually I can get away with having moderate SQL skills because my ASP and VBScript is sharp so I can do what I like the the record set one I have run the query. mmmmm a bit lazy I know.

    Can I get the desired outcome with a better query?

    TYIA

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, since you want only the date column from the TBL_Call_Staus_History, you can get the max one easily, by using GROUP BY on the outer query, but this means putting all the other columns into the GROUP BY clause

    here's an alternative --
    Code:
    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 
      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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    25
    OMG that is such a big help to me. It seems to run great with no duplicates. I am goin to have to 100% understand what you have done, I will go through it now. Looking at your SQL makes me realize how simple myn is.

    You have a most excellent day r937

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    [homer]
    mmmm.MaxDate
    [/homer]
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2008
    Posts
    25
    Good day r937 - The SQL you kindly helped me with was working without issue until today whne I run it I know get 317,000 records. I cant for the life of me work out why.

    TBL_Support_Call only has 94 records
    TBL_FollowUp only has 38 records
    TBL_Call_Staus_History has 140 records

    Any ideas?

    TYIA

  8. #8
    Join Date
    Dec 2008
    Posts
    25
    Further to the last post my original SQL (which is in my first post) gives me 105 records. Of course the objective of the report is to give as many records as there is in the TBL_Support_Call table

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do me a favour and run this --
    Code:
    SELECT TBL_Support_Call.Ref
         , COUNT(TBL_FollowUp.[Original Call Ref]) AS ooops
      FROM (
           TBL_Support_Call 
    LEFT OUTER
      JOIN TBL_FollowUp 
        ON TBL_FollowUp.[Original Call Ref] = TBL_Support_Call.Ref
           ) 
    GROUP 
        BY TBL_Support_Call.Ref 
    HAVING COUNT(TBL_FollowUp.[Original Call Ref]) > 1
    see anything unusual?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2008
    Posts
    25
    This code returns no results.

    I just cant figure out where all those records could possibly b coming from

    Thank you for your time

  11. #11
    Join Date
    Dec 2008
    Posts
    25
    Well the unexplained has happened. I placed my original query there so users didnt get 317,000 records but of course they were getting some duplicates. Just back from lunch I re-visit the issue and paste your query back in place, now its working again without issue.

    I like things working the way they should however dont like not knowing why irregular things happen......

    Thanking you again for your time )

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds like a cartesian product to me, but I'm sure r937 will fix it for ya
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it works, then it doesn't work, then it works...

    i'm not sure i can fix that

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2008
    Posts
    25
    A day later and its working without issue.

    r937 thank you for your time, very much appriciated...

  15. #15
    Join Date
    Dec 2008
    Posts
    25
    r937

    If I wanted to get a query that showed the number of calls (entries in the TBL_Support_Call table) closed per day would I need to carry out this alias type join you created also?

    Before I had the TBL_Call_Staus_History table it was nice and easy using:

    SELECT Count(TBL_Support_Call.Ref) AS CountOfRef, Format([Close Date],"dd mmmm") AS [Closed Day]
    FROM TBL_Support_Call
    WHERE (((TBL_Support_Call.Status)="Closed"))
    GROUP BY Format([Close Date],"dd mmmm")
    HAVING (((Format([Close Date],"dd mmmm")) Like "*"));

    TYIA

Posting Permissions

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