Access/SQL Query

[fini]

I've been working on a database in Access and have managed to complete confuse myself. The relevant details are:

Main table: ID, License1, License 2, License 3, License 4
License table: ID, License-type

The licenses in the main table refer to ID numbers in the license table so an average entry in the main table might be 1, 2, 3, 1, 8 . I want to create a query so that when you load it, it displays the data with the license type so for example :
1, exclusive, non-exclusive, x, y
2, non-exclusive, x, exclusive, y

I've created relationships between license 1, 2, 3 and 4 and the license ID, but can't work out how to create the query to get the data in a query as I'd like. Any ideas?

 

[fini]

Each ID will always have 4 licenses attached to it (license 1,2,3,4). There's actually more in the main table than just the license references - I left those out as they're not really relevant - but they do mean that have multiple instances of the same ID just to sort out the license issue doesn't really make sense (and would cause other problems unfortunately).

 

[fini]

No, it all works fine until I add the second inner join and then grinds to a halt, really can't work this out

EDIT: Ah ha - it was a parenthesis issue! Got it displaying 2 of them now through the use of the below - scaling up to 4 shouldn't be difficult (touch wood):

SELECT * FROM (main mt
INNER JOIN [Licenses] lt1 ON lt1.[ID] = mt.[License1]) 
INNER JOIN [Licenses] lt2 ON lt2.[ID] = mt.[License2];

EDIT: OK, can't work out how to add a third now

 

[fini]

Ah you've misunderstood Dolph. The license1, license2, license3, license4 in the main table aren't just there so I can add licenses at will - there will always be four different types of license attached to each ID. They are for different things (license1 for example details the UK license, whilst license2 details the worldwide license) - license1 etc isn't descriptive, but they are better described in the actual database. It is thus a one - many relationship between the tables. The structure you proposed wouldn't work due to license1 and license2 being for different things.

 

[fini]

No, sorry, let me explain with some fake data:

main:
ID       license1 (UK)     License2(worldwide)     license3(UK special)
---------------------------------------------------------------------------
1              1                1                       3
2              2                1                       2

license:
ID         license type
------------------------------------------------------------------------
1          exclusive
2          non-exclusive
3          non-exclusive, particular field
4          none

So each item has four separate licenses running at the same time - always being one of each kind (in the example UK, worldwide and UK special).

There is thus a one to many relationship between license.[license type] and main.license1 for example as there may be 10 different items with a UK exclusive license, but there will never be an item with a UK exclusive and non-exclusive license.

Hope that makes more sense.

Why I've been asked to do this I have no idea...

 

[fini]

Ah that's where I'm going wrong then. I've always thought it was fields for some reason.

Off to get a snack and then to start again with a link table this time.

Thanks!