Composite Primary Key, Relationships & Combo Boxes

[koolwaters]

A happy, healthy and prosperous 2011 to everyone.

I need to create a primary key field in my database to display the Current Year, Current Month and then an AutoNumber, eg:
2011011 Doe Jane
2011012 Doe John
2011013 Doe Jill
2011014 Doe Jack

2011011: 2011 (Year) 01 (Month) 1 AutoNumber

I was able to acheive this by creating three columns in the table; Activate Year, Activate Number and AutoID. I set the Activate Year and Month to Integer and the Default Value is set to: =Year(Date())+1 and =Month(Date())+1 respectively. The AutoID is simply an AutoNumber field.

The challange I am now having is with relationships and combo boxes.

Do I need to add all three columns as the foreign key in related tables?
Which column do I bound for a combo box?

Is there another way to acheive the primary keey format I want without using three columns?

Thanks for any assistance.

 

[boblarson]

My suggestion is DO NOT USE COMPOSITE KEYS. They are a PAIN IN THE @SS to use and require you to store all fields in each child table. Just use the autonumber. Why would you need to have an autonumber as part of a composite key? It makes no sense whatsoever. The autonumber will make it unique. You can store the other fields as well but it will not help to have more than the autonumber as the primary key.

 

[xenou]

The autonumber by itself would presumably be enough to guarantee uniqueness. Is this field (or "key") supposed to relate to to other tables? How would such relationships be created (I mean, in terms of data entry and maintenance, not in terms of the technicalities of creating relationships between Access tables)?

Edit: by presumably in my first sentence, I mean of course without doubt

 

[SydneyGeek]

Putting in 2 cents... As Bob and xenou have said, stick with the Autonumber.
Besides being simple to set up and requiring no maintenance on your part, it's quick to index. And there is a strong school of thought that the primary key should not 'mean' anything to the user -- or even be displayed, for that matter. It's just there for uniqueness, so the database can hook up the tables correctly.

Denis

 

[koolwaters]

Thanks for the suggestions.

I realised when I was about to establish relationships that the composite key would be a problem.

What I have done is to leave the 3 columns in the table but to just use the AutoID as the AutoNumber as suggested and on the forms, I joined the 3 fields:
=ActivateYear&ActivateMonth&AutoID so that it displays what my manager requested.

I appreciate the feedback. Thanks.

 

[Norie]

Why not add a field for the activate date and make it's default value the current date? Then forms and reports you can display it as you want using an expression.