Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Location
    NH
    Posts
    3

    Unanswered: I'm having problems with Multi-Table JOINS

    Access 2000 V9.0

    I am trying to get my head around how to perform a multi-table query so that I can lookup descriptions in other tables in a single query.

    I have an InventoryItems table that tracks Subdivision using a SubdivisionID.
    I can't for the life of me figure out how to get the SubdivisionName from the Subdivisions table in my query that also pulls data from my main table (InventoryItems).
    [ Maybe because it is Friday.....]

    Below I have illustrated the tables/fields with sample data. I am not showing all of the fields for purpose of making this easier to envision. The unlisted fields are not used for filtering/linking.

    ---------------------------------
    TABLE: InventoryItems
    ---------------------------------
    InventoryItemID ItemDesc SubdivisionID
    36 Chair
    129 Typewriter
    130 Table 2
    131 Desk

    NOTE: Not all InventoryItem records have a Subdivision which is what I believe is th emain source of my troubles.


    ---------------------------------
    Subdivisions
    ---------------------------------
    SubdivisionID SubdivisionName DivisionID
    1 West Coast 2
    2 NorthEast 2

    NOTE: Currently, I am NOT trying to do anything with the Division.
    I will work on Filtering by Division AFTER I figure out how to get the Subdivision Name.


    So......when I execute my query of InventoryItems, I want to show the Subdivision name instead of the Subdivision ID.

    BUT....
    I just can't seem to get the SubdivisionName

    Here is the query that I have come up with, but it only returns 1 record from InventoryItems (InventoryItemID = 130) - It should return all because I am not trying to filter anything out.

    SELECT I.InventoryItemID, I.ItemDesc, I.SubdivisionID, S.SubdivisionName
    FROM InventoryItems AS I, Subdivisions AS S
    LEFT OUTER JOIN S ON
    I.SubdivisionID = S.SubdivisionID

    This gets me the following Error:
    SYNTAX Error in JOIN operation


    I appreciate any assistance

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Off the top of my head:

    SELECT I.InventoryItemID, I.ItemDesc, I.SubdivisionID, S.SubdivisionName
    FROM InventoryItems AS I LEFT JOIN Subdivisions AS S ON
    I.SubdivisionID = S.SubdivisionID
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    NH
    Posts
    3
    thanks

    Merry Christmas

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Merry Christmas!

    Did that work okay?
    Paul

Posting Permissions

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