Concatenation of five fields and use to build relationship

[prestopr66]

Hello



I have recently made the jump from Excel to Access. I'm in the process of converting my Excel Spreadsheet (The Monster) to Access. I have built the tables and now establishing relationships!

The problem I have relates to two tables.
Table 1. Contains fields that build a unique name for an item from five fields.
Table 2. Contains multiple bookings against an item (five fields) in table 1.

However, I may have unnecessarily complicated the relationship as I need to add underscores between each concatenated field in table 1 for ease of viewing and deciphering by the user. But as soon as i do this it presents problems with establishing relationships!

Is the concatenation of fields without underscore relevant and placement of underscores more relevant for forms and reporting?

any assistance is appreciated

[pbaldy]

I think the rule of thumb would be that table 1 would contain an autonumber primary key field. That field would be stored in table 2 and be used to relate the tables, not the 5 fields (or a concatenation of them). You can present the 5 fields to the user any way you want, but I wouldn't relate tables with them.

[Bob Fitz]

Why not just have an Auto-number field as the primary key for table 1 which would simplify the relationship issue.
You can still make the records unique using the five other fields.

[prestopr66]

Thanks Bob
I have set the primary key as auto-number. But, how do I make the records unique using the five fields?

[Bob Fitz]

Thanks Bob
I have set the primary key as auto-number. But, how do I make the records unique using the five fields?

Take a look at these links:

https://support.microsoft.com/en-us/...0index%20name.

https://stackoverflow.com/questions/...cess-databases

For more try google: access multi field index

[prestopr66]

Hi Bob
Issue sorted! But I arrive back at the same place. How to establish a relationship between two tables;
Table 1. Field GCOB_ID (concatenation of five fields)
Table 2. Field PROD_GCOB_ID
Maybe I'm missing the fundamentals here!!

[pbaldy]

As I mentioned in post 2, you would save the autonumber from table 1 in table 2, not the concatenated field. The relationship would be on that field.

[prestopr66]

Hi Paul
i understand that would form the relationship but to the user the auto number would mean nothing! I’m must be missing something.

[isladogs]

PMFJI but Paul appears to be offline.
End users should never work directly with tables or queries.
All interaction should be done via forms.
Create a query joining the two tables using the common field discussed earlier in the thread and use that query as the record source for the form

[prestopr66]

Thanks Isladogs
All good. But how do I create a concatenated field in the form? The auto-number will mean nothing to the user.
thanks

[orange]

See this link on composite unique index for clarification.

[prestopr66]

Hi Isladogs
Just to clarify "Create a query joining the two tables using the common field discussed earlier in the thread and use that query as the record source for the form" I assume I have to create a table based on this query and then use it to build the form?

[isladogs]

No. Do not create another table.
As stated create a query joining both tables and add whichever fields you need to your query including your concatenated field.
The query forms the record source for the form.
There is no reason to display the autonumber field unless you want to ...as it has no meaning other than being a unique identifier

[prestopr66]

Thanks
OK, but something is not right. When I create the form the query is not listed to select. I also notice that the query icon itself has an exclamation mark. What does that mean??
Your help is appreciated

[prestopr66]

Ignore the last post!!!