multiple field, primary key

[Jim]

I have tblAccounts, where I have the following (each field will be
sorted on):

field1 long integer
field2 long integer
field3 text (this field can be null-see below)

Field1 & "-" & Field2 & Field3 = Field4 (text), which is the primary
key.

thus,
10 & "-" 200 & AH = Account# 10-200AH

The primary key (10-200AH) is the foreign key in several other tables.

My question is, would it be better to have field1,field2,field3 a
"multiple field primary key", which would mean that I would need to
require a zero in field3 if null and just display "" (blank) for
field3 in any reports. Thats why I didn't make the 3 fields a primary
key to begin with, because of nulls in field3.

But, I'm revisting my design for two reasons. First, in making any
queries, I have to be sure to add the "breakdown" of the account
number in order to sort correctly. Second, I have a Form where user
enters field1, field2, field3, and the afterupdate event of each is:

Field4 = Field1 & "-" & Field2 & Field3

But, I've run across a couple of instances where user somehow mananged
to not fill in complete account or whatever strange thing happened,
and the PK field (field4) didn't get updated properly.

Any suggestions would be appreciated

Thanks, Jim

 

[Michael Gramelspacher]

I have tblAccounts, where I have the following (each field will be
sorted on):

field1 long integer
field2 long integer
field3 text (this field can be null-see below)

Field1 & "-" & Field2 & Field3 = Field4 (text), which is the primary
key.

thus,
10 & "-" 200 & AH = Account# 10-200AH

The primary key (10-200AH) is the foreign key in several other tables.

My question is, would it be better to have field1,field2,field3 a
"multiple field primary key", which would mean that I would need to
require a zero in field3 if null and just display "" (blank) for
field3 in any reports. Thats why I didn't make the 3 fields a primary
key to begin with, because of nulls in field3.

But, I'm revisting my design for two reasons. First, in making any
queries, I have to be sure to add the "breakdown" of the account
number in order to sort correctly. Second, I have a Form where user
enters field1, field2, field3, and the afterupdate event of each is:

Field4 = Field1 & "-" & Field2 & Field3

But, I've run across a couple of instances where user somehow mananged
to not fill in complete account or whatever strange thing happened,
and the PK field (field4) didn't get updated properly.

Any suggestions would be appreciated

Thanks, Jim

CREATE TABLE Table1 (
partA LONG NOT NULL,
partB LONG NOT NULL,
partC TEXT(50) DEFAULT "" NOT NULL,
PRIMARY KEY (partA, partB, partC)
);

Drop the Field4 as it is a computed field and can be computed on the fly anytime needed.

Just a suggestion.

 

[Jim]

CREATE TABLE Table1 (
partA LONG NOT NULL,
partB LONG NOT NULL,
partC TEXT(50) DEFAULT "" NOT NULL,
PRIMARY KEY (partA, partB, partC)
);

Drop the Field4 as it is a computed field and can be computed on the fly anytime needed.

Just a suggestion.

Seems logical. It's bothered me that I set it up this way in the
first place, but fortunately I believe I can change it pretty easy.

Thanks.

 

[Steve]

I always recommend a primary key of autonumber for ALL tables just to avoid
the problem you describe and other problems. I recommend:
TblAccount
AccountID
Account

When you need to enter an account in any form then, all you need is a
combobox that has TblAccount as its rowsource. When you do that the only
mistake that an user can make is to fail to enter an account. That too can
be eliminated by making the AccountID required in your form.

Steve
(e-mail address removed)

 

[Jim]

uhh, I'm a bit confused. So you're recommending to keep it as I've
done, but add a (PK) autonumber field?

Thanks.

 

[dragonoff]

Thanks, but think I got it figured out now.

Jim

 

[Jim]

CREATE TABLE Table1 (
partA LONG NOT NULL,
partB LONG NOT NULL,
partC TEXT(50) DEFAULT "" NOT NULL,
PRIMARY KEY (partA, partB, partC)
);

Drop the Field4 as it is a computed field and can be computed on the fly anytime needed.

Just a suggestion.

I'm tryhing to do as you suggested, but have a question. Is there a
way to "name" what is now the composite key, so that in defining
relationships I can simply join the composite key "AccountNo" with a
similarly combined foreign key in other tables? In other words, I'm
not sure how to join on multiple fields........

thanks, Jim

 

[Michael Gramelspacher]

I'm tryhing to do as you suggested, but have a question. Is there a
way to "name" what is now the composite key, so that in defining
relationships I can simply join the composite key "AccountNo" with a
similarly combined foreign key in other tables? In other words, I'm
not sure how to join on multiple fields........

thanks, Jim

I usually do the joining in the Relationships window. Just selet the three fields in one table and
drag them to the relatedr table. Just select the fields in the related table by using the combo
boxes in the Edit Relationships window..

If you wish to avoid multiple-column keys, you can do this.

CREATE TABLE Table1 (
table1_id AUTOINCREMENT,
partA LONG NOT NULL,
partB LONG NOT NULL,
partC TEXT(50) DEFAULT "" NOT NULL,
UNIQUE (partA, partB, partC),
PRIMARY KEY (table1_id)
);

Just join then on table1_id. I find nothing particularly difficult about using multiple-column
keys.

 

[Jim]

I usually do the joining in the Relationships window. Just selet the three fields in one table and
drag them to the relatedr table. Just select the fields in the related table by using the combo
boxes in the Edit Relationships window..

If you wish to avoid multiple-column keys, you can do this.

CREATE TABLE Table1 (
table1_id AUTOINCREMENT,
partA LONG NOT NULL,
partB LONG NOT NULL,
partC TEXT(50) DEFAULT "" NOT NULL,
UNIQUE (partA, partB, partC),
PRIMARY KEY (table1_id)
);

Just join then on table1_id. I find nothing particularly difficult about using multiple-column
keys.

I was trying to make the join in the query window rather than the
relationships window. It worked once I did it in the relationships
window.

Thanks