Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Access 2010: Query/Table Linking Conundrum

    I have a Table that records 3 actions per record that use a linked table for the detailed description reasons of the actions.

    The tables have the following fields and data:

    A table recording the actions [Actions] using an action description ID:

    No code has to be inserted here.


    And the table describing the actions [Actions_Description] as:

    No code has to be inserted here.


    Now I wish to create a Report that displays the results as follows:

    No code has to be inserted here.

    In both tables the ID field is the primary key.

    How do I construct the select query that with be used as the data source for the Report?

    The conundrum that I have is that [Actions_Description].[ID] needs to be linked to each of the [Actions].[Action_A], [Actions].[Action_B] and [Actions].[Action_C] fields and I've been unable to work out how to create a query do this.

    Scratching my head...

    Trevor
    Last edited by BygAuldByrd; 2012-06-01 at 03:29.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Hi,

    This might do it;
    Code:
    SELECT A.ID, D1.Description As [Action A], D2.Description As[Action B], D3.Description as [Action C]
    FROM Actions A INNER JOIN Actions_Description D1 ON A.Action_A = D1.Action_ID
    INNER JOIN Actions_Description D2 ON A.Action_B = D2.Action_ID
    INNER JOIN Actions_Description D3 ON A.Action_C = D3.Action_ID
    The trick lies with adding Actions_Description 3 times, each time with a different Alias (D1, D2, D3).
    Last edited by ruirib; 2012-06-01 at 04:36.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Hi Ruirib,

    Thanks for your prompt reply but I'm not completely understanding what you are doing, as I've not been able to replicate it.

    In particular where does the "A.ID" come from in the SELECT instruction? Do you mean this to be "Actions.ID"?

    Also, is the [Action A] etc supposed to have the space between "Action" and "A", ie are you using the Report column header as the descriptor.

    Your assistance is very much appreciated

    Cheers

    Trevor

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Hi,

    You just need to copy my code, if those are your column names, but what I am doing is using alias for the tables names. I always use them with longer SQL statements as they make it easier when writing such statements.

    Notice the FROM clause I used: (...) FROM Actions A (...).

    Here I am using table Actions and giving that table the alias A. When you use an alias, any reference to the table is changed to the alias. So, instead of writing Actions.ID, I write A.ID instead. Simpler to write .

    Alias are mandatory when you include a given table more than once in a statement. Here table Actions_descriptions is added 3 times to the FROM clause. The first reference was aliased by me as D1, the 2nd as D2 and the 3rd as D3.

    I use [Action A] precisely because of the space. If it didn't have the space, you didn't need the [] pair. If you choose to do without the spaces, you can remove the []​.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    When I copy and try to run your code it gives me the following error:

    Attachment 31531

    What is missing?

    Cheers

    Trevor
    Attached Images Attached Images

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Access is a big bore with its requirement for parenthesis in joins. Here is the Access acceptable syntax:

    Code:
    SELECT A.ID, D1.Description AS [Action_ A], D2.Description AS [Action_B], D3.Description AS [Action_C]
    FROM Actions_Description AS D3 INNER JOIN (Actions_Description AS D2 INNER JOIN (Actions_Description AS D1 INNER JOIN Actions AS A ON D1.Action_ID = A.Action_A) ON D2.Action_ID = A.Action_B) ON D3.Action_ID = A.Action_C;

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Hi Ruirib,

    Again, many thanks for your assistance, it pointed me in the right direction.

    That latest coding you sent posted worked, as did the following coding that I stumbled upon in my searching for a solution:

    SELECT Actions.ID,
    D1.Description As [ActionA],
    D2.Description As [ActionB],
    D3.Description As [ActionC]
    FROM ((Actions
    INNER JOIN Actions_Description D1 ON Actions.Action_A = D1.Action_ID)
    INNER JOIN Actions_Description D2 ON Actions.Action_B = D2.Action_ID)
    INNER JOIN Actions_Description D3 ON Actions.Action_C = D3.Action_ID

    Again it was parenthesis that resolved the issue.

    Cheers

    Trevor

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Glad to be of 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
  •