Access 2010: Query/Table Linking Conundrum

[BygAuldByrd]

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:

ID	Action_A	Action_B	Action_C
1	1	1	2
2	2	2	1
3	3	3	3

And the table describing the actions [Actions_Description] as:

Action_ID	Description
1	Walk
2	Run
3	Jump

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

ID	Action A	Action B	Action C
1	Walk	Walk	Run
2	Run	Run	Walk
3	Jump	Jump	Jump

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

[ruirib]

Hi,

This might do it;

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).

[BygAuldByrd]

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

[ruirib]

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 []​.

[BygAuldByrd]

When I copy and try to run your code it gives me the following error:

Attachment 31531

What is missing?

Cheers

Trevor

[ruirib]

Access is a big bore with its requirement for parenthesis in joins. Here is the Access acceptable syntax:

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;

[BygAuldByrd]

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

[ruirib]

Glad to be of help.