Hi folks,

New person to the forums here. I've had a scroll through the FAQs and can't see an answer to a problem I need to solve. I wonder if there's anyone could offer some help?

My db has three tables: table_a, table_b and table_c. Each table has the following fields:

table_a
a_id (PK)
a

table_b
b_id (PK)
b

table_c
c_id (PK)
a_id (FK (many to one))
b_id (FK (many to one))
c

I would like field c in table c to be a concatenation [table_a].[a]+"-"+[table_b].[b]. I'd like this field to automatically generate and be constrained as unique. Creation of value c in table_c should be by selection of value a & then value b in a form based on table_c.

Can anyone advise me how I do this?

By the way - editing to add this - I am using MS Access 2000 & can't upgrade to a later version.

It appears that what you have is a join table for a many to many relation. Value A can have multiple value Bs and vice versa. So your join table lists each combination. Obviously you don't want any combination listed more than once.

However, there are better ways. You can set a multi-field index ion the combination of a-Id and b-Id. Setting the index to no Duplicates will prevent such a duplication. There is NO need to concatenate the two IDs and especially no reason to store that value. If you do want to display the concatenated value, just do it on the fly witht he expression:

=[A-ID] & "-" & [B-ID]

If your business rules say that a is unique within table-a and b is unique within table-b, you need to define unique indexes on them in those tables. The autonumber can (and should) still be the primary key. Given that scenario, a unique index on a-ID and b-ID in the junction table will be fine. However, if a is not unique in table-a and/or b is not unique in table-b, then you will have to duplicate data in the junction table in order to create a unique index there. Now the bad news, this duplication violates second normal form and if a is changed in table-a or b is changed in table-b, the duplicated values in the junction table will become INVALID. So, you must be extremely careful to manage any updates of this type yourself.

Thanks, some interesting info.

The way I'm using the three tables is to describe locations. Table_a describes furniture and table_b describes rooms. Furniture is a bit generic (e.g. 'table' or 'chair') whereas rooms are unique (e.g. 'kitchen', 'lounge'). My purpose is to create a record for a unique location (e.g. 'table - kitchen' or 'chair1 - lounge') that can be created by users from a pair of pull-down lists.

Each furniture item is unique within table_a and each room is unique within table_b - and both carry more data than just the name, of course. Further the individual group created by concatenating the two must also be unique (and must be queryable). Can I do this by creating on-the-fly as suggested? I shall have to think.

Very grateful for both replies.

This just confirms that what you have is a join table. However, you have a bit of a problem. Can't a room have more than one of the same item of furniture? For example:

Dining Room-Dining Chair

The way I would get around that is to add a quantity field to your join table. This would still allow you to make the combination of A-ID and B-ID unique but still allow you to record multiples.

Yes, you can query this. Though I would imagine that you would not need to query the combination. For example, I can see you wanting to see all furniture in a room or what rooms a specific item of furniture is in. But I don't see much value in querying for all living room-sofas.

This just confirms that what you have is a join table.
Thought it might.

Can't a room have more than one of the same item of furniture?
In this instance, as it happens they can't.

I don't see much value in querying for all living room-sofas.
There is. In this case it might be to answer a question like "tell me all item names, colour, height where purpose is 'kitchen - table' and covering is formica".

There is. In this case it might be to answer a question like "tell me all item names, colour, height where purpose is 'kitchen - table' and covering is formica".

See now you are adding something into the mix. I still don't see any value of searching on 'Kitchen - table', but when you add the covering you are adding something else.

But in this case your SQL might look something like this:

SELECT Furniture, Colour, Height, Covering FROM tblFurniture Inner Join tblFurniture.FurnitureID = tjxRoomFurniture.FurnitureID
WHERE tjxRoomFurniture.RoomID = Kitchen AND tjxRoomFurniture.FurnitureID = Table AND Covering = Formica;

You would probably be using codes rather then the text in that WHERE clause but you should get the idea. The point is you don't need a field to combine the 2 in the join table.

See now you are adding something into the mix.
Absolutely - didn't want to put entire content of tables in the question :)

The point is you don't need a field to combine the 2 in the join table.
OK! Thanks for the help!

glad to assist