Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Can anyone help please with a query question?

    I have a query which already made of few tables, and i wanted another table from which I need to retrieve info of one field only but as I add this new table called "ServiceLanguageInterpreter" and when I want to click on view I get this message
    " The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that perform the first join and then include that query in your SQL statement"
    The thing is I created another query based on two tables adding the two fields which i need, and then tried to add this new query in the main query and I got the same message.

    Any help would be much appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's your gepflugten table, it isn't joined to your verschmitten table correctly

    if those weren't the right table names, i apologize, my crystal ball is broken and i'm having a hard time seeing your sql from here

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

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi R937, Could you be a bit more clear on clarifying
    "gepflugten table"
    and
    "Verschmitten table"
    as I dont know what do they mean?

    My apologiese for long table name but that was exactly the right table name which I posted in here.
    Last edited by Emal; 12-25-06 at 13:31.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i guess i was a little bit too subtle, eh?

    we can't help you if you don't show your query

    if you don't show your query, how are we supposed to know what's wrong?

    what's wrong with your query? i don't know, because i can't see it

    i can't see your query, so i don't know what's wrong with it

    if you wish to receive help with your query, you must show your query

    if you don't show your query, how can we help you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2006
    Posts
    386
    Hi, here is the query. I am sorry it is too long to read but I hope you dont get bored with it. Thanks alot!

    SELECT Booking.NoOfWords, Booking.TotalTrans, Booking.Id, Language.Language AS JobLanguage, Client.Name AS Client_name, Booking.ServiceID, Booking.Language1ID, Booking.Language2ID, Booking.InterpreterID, Booking.DateOfJob, Booking.TimeOfJob, Booking.BookingAddressID, Booking.UserIDEnquiry, Booking.Total, Booking.InvoiceDate, Booking.WorkHours, Booking.InterpretersRate, Booking.InterpreterTotal, Booking.ClientAmountLessVAT, Booking.ChequeNo, Client.curRate4DisLanguages, Client.curRateOther
    FROM [Language] INNER JOIN (BookingAddress INNER JOIN (Interpreter RIGHT JOIN (Client INNER JOIN Booking ON Client.id = Booking.ClientID) ON Interpreter.id = Booking.InterpreterID) ON BookingAddress.id = Booking.BookingAddressID) ON Language.id = Booking.Language1ID
    ORDER BY Booking.DateOfJob;
    I would really appreciate your help!
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What r937 is saying, Emal, in his subtle (ahem) way, is that you wouldn't call up your auto mechanic and say "My car won't start, what's wrong with it?" and expect him to give you an answer! We need to see your SQL statement from your query.

    Go into the Design Mode for your query, go up to your menu bar and click on View then click on SQL View. It'll usually come up selected (hi-lited) simply right click and copy it, then come back here and paste it into your post.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it like this --
    Code:
    SELECT Booking.NoOfWords
         , Booking.TotalTrans
         , Booking.Id
         , Language.Language AS JobLanguage
         , Client.Name AS Client_name
         , Booking.ServiceID
         , Booking.Language1ID
         , Booking.Language2ID
         , Booking.InterpreterID
         , Booking.DateOfJob
         , Booking.TimeOfJob
         , Booking.BookingAddressID
         , Booking.UserIDEnquiry
         , Booking.Total
         , Booking.InvoiceDate
         , Booking.WorkHours
         , Booking.InterpretersRate
         , Booking.InterpreterTotal
         , Booking.ClientAmountLessVAT
         , Booking.ChequeNo
         , Client.curRate4DisLanguages
         , Client.curRateOther
      FROM (((
           Booking
    INNER
      JOIN Language
        ON Language.id = Booking.Language1ID
           )   
    INNER 
      JOIN BookingAddress 
        ON BookingAddress.id = Booking.BookingAddressID
           ) 
    INNER 
      JOIN Interpreter 
        ON Interpreter.id = Booking.InterpreterID
           ) 
    INNER 
      JOIN Client 
        ON Client.id = Booking.ClientID
    ORDER 
        BY Booking.DateOfJob
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2006
    Posts
    386
    I tried and copied and pasted all the above as you. Like Below:

    SELECT Booking.NoOfWords , Booking.TotalTrans , Booking.Id , Language.Language AS JobLanguage , Client.Name AS Client_name , Booking.ServiceID , Booking.Language1ID , Booking.Language2ID
    , Booking.InterpreterID , Booking.DateOfJob , Booking.TimeOfJob , Booking.BookingAddressID , Booking.UserIDEnquiry , Booking.Total , Booking.InvoiceDate , Booking.WorkHours , Booking.InterpretersRate , Booking.InterpreterTotal , Booking.ClientAmountLessVAT , Booking.ChequeNo , Client.curRate4DisLanguages , Client.curRateOther FROM ((( Booking INNER JOIN Language ON Language.id = Booking.Language1ID) INNER JOIN BookingAddress ON BookingAddress.id = Booking.BookingAddressID ) INNER JOIN Interpreter ON Interpreter.id = Booking.InterpreterID )
    INNER JOIN Client ON Client.id = Booking.ClientID ORDER BY Booking.DateOfJob
    But when i view it in design view all the relationships are deleted. Lets assume, I create the relationships again, Will I be able to add the new table?
    Emi-UK
    Love begets Love, Help Begets Help

  9. #9
    Join Date
    May 2006
    Posts
    386
    I tried to view the query and I got this message
    " Can't represent the join expression Language.id = Booking.Language1ID. in design view
    I get the same message for
    " BookingAddress.id =Booking.BookingAddressID"
    and "
    Interpreter.id = Booking.InterpreterID"
    Thank you for your time.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happened when you ran my query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2006
    Posts
    386
    I got those 3 above mentioned errors
    Emi-UK
    Love begets Love, Help Begets Help

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Can't represent the join expression" is not a run-time error

    did the query work, right after you pasted it, but before you tried to go to Design View?

    if not, erase the query, start a new one, paste my sql, and then please run it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2006
    Posts
    386
    Right after I pasted your query into SQL view of the query, it didnt work as they wore written like paragraphs so I went to design view. Let me tell you what error did I get.
    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
    it didnt work as they wore written like paragraphs
    "written like paragraphs"???????

    i've never seen that error message before

    please, just paste, then run
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    May 2006
    Posts
    386
    Ok, I just pasted it and run it, It ran fine except I got a message "enter parameter" user.id but it was fine otherwise. BUt when I clicked in design view I got the following error message "
    can't represent the join expression Language.id = Booking.Language1ID in Design View"
    same error was given for Interpreter.id and Booking.Id. thanks
    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
  •