concatenating multiple fields within same table?

[BadDog]

I am trying to combine multiple fields in the same table to create the Primary Key field. Example, combining "Chapter" "Section" "Page" "Filename" and have the new field separate each of these with an "-". So, the resulting field would be "[chapter]-[Section]-[Page]-[Filename]" Each field is two digits except the filename field. I am pretty sure I could do this with two separate tables, but is there a way to do it within the same table?

 

[dawnd3]

I think this might work. Create a query with the table that has the fields that you want to concatenate. Make it an update query. In the query grid, bring down the new field for the unique ID. In the update to field type [field1]&"-"&[field2]&"-"&[field3] And so on. Putting your field names in place of my fields of course. Basically you just put an & in between each string you want to bring together. Then click the run button and it should update all records with the data in your other fields.

http://www.calliopeconsulting.net

 

[Steve101]

You can create a Primary Key in a table which comprises more than one field. In design view of the table, simply select the first field by pointing to the left of the field name, then hold down the control key, and click on the other fields which collectively will make up the "composite" Primary key. Then click on the Primary Key button on the toolbar, and you will have created a composite primary key.

Using this method forces uniqueness at the table level of the combination of the fields.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BadDog]

Steve101, if I use the composite primary key method how can I display the composite primary key, like in another table or form?

 

[Steve101]

BadDog,

You are moving into fairly interesting territory here. For new systems that I develop, I never use 'real' data as Primary Keys. I always use an autonumber integer provided by the system (and internal to the system) as the Primary Key. The benefits of this are that:

(a) Tables never have composite keys
(b) Foreign Keys only have to be a single field
(c) Cascading updates through tables are never necessary, as linkages are not through 'real data'.
(d) Can get performance and storage improvements because of key uniqueness and size.

To 'protect' data against 'composite key duplicates', you can still set up additional unique keys based on the real field or fields.

The 'down' side to using autonumber fields as primary keys is:

(a) Its conceptually more difficult to 'follow the data', when you're just looking at it/setting it up 'in the raw'.

(b) 'Tracking' through the data may require additional reference tables in the associated queries, as the autonumber fields themselves are meaningless, and really just provide inter table linkages.

So .... to answer your question "how can I display the composite primary key, like in another table", you simply would use the composite key component(s) as the join fields between the primary and foreign key tables. In query designer, just drag a line between each of the relating fields. If you use the single key approach that Ive discussed above though, you would never have more than one link between two tables (with one or two minor exceptions which are beyond the scope of this post).

Hope this makes sense, otherwise post back


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)