Page 1 of 4 123 ... LastLast
Results 1 to 15 of 53
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Any idea why I have duplicate IDs in query but not in the source table?

    Dear Friends,
    I have purchased the product that removes duplicate values from ms access. but when I run it it only gives me access to tables. The problem is that when I look at the query there are too many duplicate IDs and strangely when I look at the source table then there is no duplicate IDs.

    Could some one please help? When I want to print a report which is sourced from the query, then it prints duplicate lines of informations because of the duplicate ids.

    Any help would be greatly appreciated.

    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query incorrectly joins to the gerpflugen table, when in fact it should be joining to the versplatzen table

    if that isn't exactly right, it's because my crystal ball isn't working correctly this morning

    stupid $%*#& crystal ball!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2006
    Posts
    386
    I couldn't get the meaning for gerpflugen table and versplatzen table.
    Could you please clearly explain that?
    Thanks
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please allow me to translate - Rudy is asking for the SQL you are using. I imagine he would also like to know something about the tables involved (names, primary key field(s)) and their relationships to each other
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2006
    Posts
    386
    Thanks pootle,
    The tables related to the query (qryinvoices) are called: Booking, Invoice, and some other ones as you can see in the attached screenshot that I have taken of the relationships. I think it is only the id from booking table and Invoice table that are causing this.
    The relationship between the id from booking table and bookingid from Invoice table is also shown in the screenshot.

    I really appreciate any help.
    Thanks
    Attached Thumbnails Attached Thumbnails Screenshots of Tables and Relationships.JPG  
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    relationships look fine

    still not enough info to explain why the query produces "too many duplicate IDs"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2006
    Posts
    386
    There are at least over 1000 duplicate ids in the query but in the table there isnt any duplicate. and the strange thing is that not all records have duplicate values. It is about 1000 from 12000 records that have duplicate values.

    I really need help. Thanks again.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me be just a wee bit more explicit: SHOW. YOUR. QUERY. PLEASE.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2006
    Posts
    386
    How do you want to see it please?
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Go to query design view -> SQL View -> Copy and paste all the text (the code behind all the pretty pictures)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2006
    Posts
    386
    thanks pootle, but no pretty pictures. All boring tasks!
    I have just noticed that the duplicate values are not just in one query but in two possible in more queries. but the main query is called "qryinvoice" and from this "qryinvoice" I have made another queries. The first below SQL query is from the main qry called "qryinvoices"

    SELECT Booking.TrvlTim4Inter, Client.JilcsOrderNo, Booking.Id, BookingAddress.Address, Booking.Bonus4Inters, Booking.GenderOfInterpreter, Booking.RejectedByUs, Booking.JilcsRefNo, Booking.DNA, Client.ClientsNote, Booking.Interpreterdidntattend, Invoice.id, Interpreter.Phone, Interpreter.Email, Interpreter.Phone2, Interpreter.Mobile, Client.id, Booking.WorkHrsClient, Booking.[2ndWorkHrsClient], Booking.JournyMilesClient, Booking.Note4PMT, Booking.JourneyHours, Booking.TimeOfJob, Booking.BSLRate2Client, Booking.NoOfHoursBooked, Booking.BslWorkedHrsClient, Booking.SageInvNumber, Booking.ClientPaid, Booking.DatePaymentReecived, Booking.Id, Booking.ClientID, Booking.Language1ID, Booking.InterpreterID, Booking.ClientJobReferenceNumber, Booking.DateOfJob, Booking.BookingAddressID, BookingAddress.Address, Booking.ClientClientName, Booking.Notes, Booking.InvoiceDate, Booking.InterpreterExpenses, Booking.ClientExpenses, Booking.WorkHours, Booking.Intr2ndWrkHrsNAsian, Interpreter.Inter2ndHrRateNAsian, [InterpretersRate]/2 AS TrvlRate4Intr, Booking.TrvlTim4Inter*[TrvlRate4Intr] AS TotalTrvlTim4Inter, Booking.InterpreterRatePerMile, Booking.JourneyMiles, Booking.InterpreterTotal, Booking.InterpreterPaid, Client.curRateAsian, Client.curRateOther, Client.[2ndHrRate], Booking.ClientRatePerMile, Booking.ClientAmountLessVAT, Booking.ChequeNo, Booking.ClientPaid, IIf(Language.Asian=Yes,[curRateAsian],[curRateOther]) AS ClientRate, [ClientRate]/2 AS Rate4JourneyTime, Booking.JourneyHours*[Rate4JourneyTime] AS TotalJourneyAmount, Booking.InterpreterID, Client.Name, Interpreter.TelPhoneIntrRate, Booking.TelHrWorkedInter, Client.TelPoneRate, Booking.TelHrWorkedClient, BookingAddress.Address, Booking.SpecialNote4Invoices, Invoice.*, Invoice.*, Interpreter.Surname, Interpreter.Address1, Interpreter.Address2, Interpreter.Address3, Interpreter.Address4, Interpreter.PostCode
    FROM (([Language] INNER JOIN (Client INNER JOIN (Booking LEFT JOIN Invoice ON Booking.Id = Invoice.Bookingid) ON Client.id = Booking.ClientID) ON Language.id = Booking.Language1ID) INNER JOIN Interpreter ON Booking.InterpreterID = Interpreter.id) INNER JOIN BookingAddress ON Booking.BookingAddressID = BookingAddress.id
    ORDER BY Booking.DateOfJob;
    And the one in the below is from another query that I also use to print statments is from the qry called " qryclientstatment"

    SELECT Booking.ClientID, Booking.ClientJobReferenceNumber, Booking.DateOfJob, Booking.ClientClientName, Booking.BookingAddressID, Booking.Language1ID, Booking.TimeOfJob, Booking.NoOfHoursBooked, Booking.WorkHrsClient, Client.Name AS ClientName, Booking.SageInvNumber, Booking.ClientRate, Booking.ClientExpenses, Booking.InvoiceDate, Booking.ClientRate, Client.curRateOther, Client.curRateAsian, Booking.[2ndWorkHrsClient], Client.[2ndHrRate], Booking.JournyMilesClient, Booking.ClientRatePerMile, Client.Name, Client.[Contact Name], Client.Address1, Client.Address2, Client.Address2, Client.Address3, Client.Address4, Client.[post code], Booking.InterpreterID, Booking.StatementRaised, Booking.ClientAmountLessVAT, Client.Name
    FROM (([Language] INNER JOIN (Client INNER JOIN (Booking LEFT JOIN Invoice ON Booking.Id = Invoice.Bookingid) ON Client.id = Booking.ClientID) ON Language.id = Booking.Language1ID) INNER JOIN Interpreter ON Booking.InterpreterID = Interpreter.id) INNER JOIN BookingAddress ON Booking.BookingAddressID = BookingAddress.id
    WHERE (((Booking.ClientID)=[Forms]![frmClientInterpreterStatements]![cboClientID]) AND ((Booking.SageInvNumber) Is Not Null) AND ((Booking.InvoiceDate) Between [Forms]![frmClientInterpreterStatements]![lstFrom] And [Forms]![frmClientInterpreterStatements]![lstTo]) AND ((Booking.CancelledByUs)=No))
    ORDER BY Booking.SageInvNumber, Booking.InvoiceDate;
    Emi-UK
    Love begets Love, Help Begets Help

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is the part of your query which might be producing the "duplicate" ids --
    Code:
    FROM (([Language] INNER JOIN (Client INNER JOIN (Booking LEFT JOIN Invoice ON Booking.Id = Invoice.Bookingid) ON Client.id = Booking.ClientID) ON Language.id = Booking.Language1ID) INNER JOIN Interpreter ON Booking.InterpreterID = Interpreter.id) INNER JOIN BookingAddress ON Booking.BookingAddressID = BookingAddress.id
    here it is again, with formatting --
    Code:
      FROM ((
           [Language] 
    INNER 
      JOIN (
           Client 
    INNER 
      JOIN (
           Booking 
    LEFT 
      JOIN Invoice 
        ON Booking.Id = Invoice.Bookingid
           ) 
        ON Client.id = Booking.ClientID
           ) 
        ON Language.id = Booking.Language1ID
           ) 
    INNER 
      JOIN Interpreter 
        ON Booking.InterpreterID = Interpreter.id
           ) 
    INNER 
      JOIN BookingAddress 
        ON Booking.BookingAddressID = BookingAddress.id
    and this is the way i would write it --
    Code:
      FROM ((((
           Booking
    inner
      join Language 
        on Language.id       = Booking.Language1ID
           )
    inner 
      join Client
        on Client.id         = Booking.ClientID 
           )
    inner 
      join Interpreter 
        on Interpreter.id    = Booking.InterpreterID
           )
    inner 
      join BookingAddress 
        on BookingAddress.id = Booking.BookingAddressID       
           )
    left outer
      join Invoice 
        on Invoice.Bookingid = Booking.Id
    notice that the INNER JOINs come first, followed by the LEFT OUTER JOIN, and that the join for each table relates back to the Booking table

    now to your problem

    if there are "duplicates" then i'm going to take a guess and say that there are some bookings which have more than one invoice

    is this the case?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2006
    Posts
    386
    First of all let me really thank you for looking at everything in more details.

    I am sorry to say that I am not good with SQL at all and therefore wouldnt really know how to resolve this? Would it be possible for you to kind change the SQL settings and post it in here so that I just copy and paste it? Also if you could please tell me which one the sql belongs to? (qryInvoice OR qryClientStatement)?

    With regards to duplicate ids, all bookings have and must have one invoice only.

    Thank you so much for your help.
    Emi-UK
    Love begets Love, Help Begets Help

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Emal
    Also if you could please tell me which one the sql belongs to? (qryInvoice OR qryClientStatement)?
    both have identical FROM clauses, don't they


    Quote Originally Posted by Emal
    With regards to duplicate ids, all bookings have and must have one invoice only.
    then perhaps you might be so kind as to show a few rows of query results which illustrate the "duplicates" problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    May 2006
    Posts
    386
    Yes they have identicla FROM Clauses.

    Do you want to see the queries in design view or in datasheet view? Then here is the screenshot of the query with a bit explanation on the right side of the screenshot.

    Thanks for your help.
    Attached Thumbnails Attached Thumbnails Query Screenshot.JPG  
    Last edited by Emal; 10-06-06 at 12:47.
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

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