Conversion of Unusual Compound Primary Key ?

[samdatum]

First, let me state first that I inherited this breeding system and design. Slowly, I'm try to make changes changes to make it more flexible. It is used by only 2-3 people but it is important to our company research work and holds 20-years data. >> I would like to convert a compound primary key into an autonumber type. I want a table [tblPedigrees] to identify unique pedigrees with a number. Currently, a pedigree consists of these independent fields:

CropsID: Number (1-4)
Type_Cross: Text, Any 1 of ~20 Single Letters
Cross_No: Text (6), 6 digits (eg. 005533) manual incremented
Sel: Text (1), Any 1 of ~20 Single Letters
Pol: Text (1), Any 1 of ~20 Single Letters
Met: Text (1), Any 1 of ~20 Single Letters
SelNo: Text (4), A combination of two parts

Example: X004988DAS0901

My immediate question is about the [Met] field. we use alpha codes: B,C,D,P,Y. For this group we behave one way & SelNo does not require a change. For codes M or S, a change to SelNo is required. The importance is in tracking. If we give every change in MET code a unique Pedigree ID, then I figure tracking will stop with each change. I thought I would create two groups of MET codes and generate a new PedigreeID only when there is a change from one group to another. This is probably not clear at all but I'll give it a try. This is plant breeding but maybe it will help to think of breeding horses or dogs.

I'm afraid I'm not experienced with SQL and work primarily with the grid, etc.

Thanks for any guidance!

 

[genomon]

I would like to convert a compound primary key into an autonumber type.

What is the clustered primary now, and why do you need to replace it?

Beir bua agus beannacht!

 

[samdatum]

Now it is the combination of the 7 fields I listed; see example at end of that list and again here (X004988DAS0901). Why? I have thought, perhaps incorrectly, that it is clumsy and requires all 7 fields participate in relationships as opposed to just a single number representing them. I thought it would easier to manage with just the number. I thought it also might make certain queries faster(?)..Thanks for your reply.

 

[genomon]

Gotcha. I'm not a big fan of autonumbers, especially on large databases. I always suggest indexing and experimenting with clustered primary keys first. If you are fortunate, dhookum will add to this thread - he is brilliant with this stuff, and an MVP. In case he doesn't here is his website, and another you might find helpful:

http://www.access.hookom.net/

http://www.mvps.org/access/

Beir bua agus beannacht!

 

[samdatum]

Thank you. I appreciate your comment about autonumbers. People seem to be strongly split on this topic and I could use help deciding. Thanks for the links genomon.

 

[dhookom]

I love autonumber fields and use them in almost every table I create whether Access or SQL Server.

Backup your database.

I would add an autonumber field to tblPedigrees (I assume this is your "master" table). Create a unique index on this autonumber field. Leave the primary key as it is now on the multiple fields.

Add a long integer field to all the related tables.

Create update queries for each of the related tables to the master table and update the long integer field to the autonumber field value.

You will then need to find and replace everywhere in your application where you have created joins on the compound primary key and change it to use the autonumber and long integer fields. Make sure you look at the Link Master/Child for subforms and subreports. Also check all joins in queries.

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

I love autonumber fields

I knew it

Here is an alternate viewpoint:

http://allenbrowne.com/ser-40.html

BTW samdatum, there is a reason dhookum is an MVP (actually probably many).

Beir bua agus beannacht!

 

[dhookom]

I don't recall every having issues with autonumbers as primary keys. With all the questions I read through in forums and news groups, I don't recal this being an issue. I would hate to maintain a multi-field join particularly if there are more than just a couple field defined as the primary key. You can still create a unique index on the multiple fields to maintain data integrity.

Duane
Hook'D on Access
MS Access MVP

 

[samdatum]

Thank you for your response(s). That was, I think, what I wanted to hear. I did not expect keeping the composite as the primary key, though. I'll give this a try...I may be back for more help later.

Thanks again to both of you for helping. I really appreciate it.

 

[samdatum]

I almost forgot. My original Q was about grouping within one component of a composite key. It probably doesn't make sense. From my original post...

My immediate question is about the [Met] field. we use alpha codes: B,C,D,P,Y. For this group we behave one way & SelNo does not require a change. For codes M or S, a change to SelNo is required. The importance is in tracking. If we give every change in MET code a unique Pedigree ID, then I figure tracking history will stop with each change. I thought I would create two groups of MET codes and generate a new PedigreeID only when there is a change from one group to another. This is probably not clear at all but I'll give it a try. This is plant breeding but maybe it will help to think of breeding horses or dogs.

Any comments on this question? Thanks

 

[dhookom]

You lost me.

Duane
Hook'D on Access
MS Access MVP

 

[samdatum]

Right.

The MET field refers to the harvest method. We keep track of several different ways. Some, but not all METs, mean a new pedigree has been created. In order to maintain the ability to track history & performance, I want to keep the same pedigree if the MET is IN(B,C,D,P,Y). If it is an M or S then we create a new pedigree. Is there a way to create new Pedigree_ID only under certain conditions, even though the combination will in fact be different?
For example,
X004988DAS0901 was new but X004988DAB0901 is not.

Thank you.

 

[dhookom]

Creating a new autonumber primary key doesn't mean you can't keep the same functionality you have always had with the existing fields.

Duane
Hook'D on Access
MS Access MVP

 

[samdatum]

Alright. Thanks!