Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Question Unanswered: Problems with joins

    Hi,

    Am relatively new to SQL with access and this is the most complicated thing I've attemted so far.

    I'm trying to code for a web interface to a library database and need to be able to show records of the items in the library, this is the simple bit. I also need to show whether the book is checked out or not and this info resides in another table. The 2 tables have a common ID 'ITEM#' I've worked out that I need a join but can't make the statement work, it just says Join not supported.
    [Microsoft][ODBC Microsoft Access Driver] Join expression not supported

    Statement:
    "SELECT Inventory.DESC, Inventory.ALT_NO, Inventory.USER1, Inventory.Mfr, Inventory.Model, 'Inventory by location.CheckedOut'

    FROM 'Inventory' INNER JOIN 'Inventory by location' ON 'Inventory.ITEM#' = 'Inventory by location.ITEM#'

    WHERE Inventory.Category = 'Book' ORDER BY Inventory.DESC ASC "

    I've split the statement up a bit to make it easier to read, as you can see I also have table names with spaces, I think the 'table name' syntax works ok but could someone back me up on that?

    Any help would be much appreciated!!

    Thanks

    Chris

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DESC is a reserved word, change it to DESCR

    use square brackets, not quotes, around table names with spaces in them
    Code:
    SELECT Inventory.DESCR
         , Inventory.ALT_NO
         , Inventory.USER1
         , Inventory.Mfr
         , Inventory.Model
         , [Inventory by location].CheckedOut
      FROM Inventory
    INNER 
      JOIN [Inventory by location]
        ON Inventory.ITEM# 
         = [Inventory by location].ITEM#
     WHERE Inventory.Category = 'Book' 
    ORDER 
        BY Inventory.DESCR ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Posts
    4

    Thumbs up

    Thanks for the quick post...

    Unfortunately the database is part of an application which relies on the field names not changing. Is there any way around this? Could I use the AS command to solve it?

    It appears to be quite an oversite on the part of the people who sold me the software!!

    Thanks again for the help

    Chris

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by wiggy
    Thanks for the quick post...

    Unfortunately the database is part of an application which relies on the field names not changing. Is there any way around this? Could I use the AS command to solve it?

    It appears to be quite an oversite on the part of the people who sold me the software!!

    Thanks again for the help

    Chris
    normaly enclosing fieldnames with [] gets around most of those problems, is there a reason for all the single quotes around records because i can't think of any that apply in your situation and it might be a cause of the symptoms your seeing, you could you try replacing them with [] to test this
    Last edited by m.timoney; 07-09-03 at 08:43.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Jul 2003
    Posts
    4

    Unhappy

    Also, regardless of the problem with DESC and DESCR when I put your code in I get the following error.

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Inventory.ITEM# = [Inventory by location].ITEM#'

    Here is and output of the sql string being passed to the execute command:

    SELECT Inventory.DESCR, Inventory.ALT_NO, Inventory.USER1, Inventory.Mfr, Inventory.Model, [Inventory by location].CheckedOut FROM Inventory INNER JOIN [Inventory by location] ON Inventory.ITEM# = [Inventory by location].ITEM# WHERE Inventory.Category = 'Book' ORDER BY Inventory.DESCR ASC

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by wiggy
    Also, regardless of the problem with DESC and DESCR when I put your code in I get the following error.

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Inventory.ITEM# = [Inventory by location].ITEM#'

    Here is and output of the sql string being passed to the execute command:

    SELECT Inventory.DESCR, Inventory.ALT_NO, Inventory.USER1, Inventory.Mfr, Inventory.Model, [Inventory by location].CheckedOut FROM Inventory INNER JOIN [Inventory by location] ON Inventory.ITEM# = [Inventory by location].ITEM# WHERE Inventory.Category = 'Book' ORDER BY Inventory.DESCR ASC
    apart from the fact i don't have the tables this works

    SELECT [Inventory].[DESC], [Inventory].[ALT_NO], [Inventory].[USER1], [Inventory].[Mfr], [Inventory].[Model], [Inventory by location].[CheckedOut]
    FROM [Inventory] INNER JOIN [Inventory by location] ON [Inventory].[ITEM#] = [Inventory by location].[ITEM#]
    WHERE [Inventory].[Category] = 'Book'
    ORDER BY [Inventory].[DESC] ASC;

    so the problem is the # marks and enclosing it in [] fixes it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Jul 2003
    Posts
    4

    Cool

    Cheers M.Timoney!

    That works a treat, at least it's executing the sql now, now I can go through and debug my thousands of othe stupid errors!

    Thanks for all the help folks!!

    Chris

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by wiggy
    Cheers M.Timoney!

    That works a treat, at least it's executing the sql now, now I can go through and debug my thousands of othe stupid errors!

    Thanks for all the help folks!!

    Chris
    no problem personely i put all table and field names in [] it not only stops lots of errors which are pains in the never regions but also makes your code easier to read
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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