multiple field, primary key

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

On Wed, 04 Mar 2009 08:24:25 -0700, Jim wrote:

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.

On Wed, 04 Mar 2009 10:28:47 -0600, Michael Gramelspacher
wrote:

On Wed, 04 Mar 2009 08:24:25 -0700, Jim wrote:

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.

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.

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



"Jim" wrote in message
...
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

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

Thanks.


On Wed, 4 Mar 2009 17:36:44 -0500, "Steve"
wrote:

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



"Jim" wrote in message
.. .
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

No, I'm suggesting a table that looks like:
TblAccount
AccountID
AccountNumber
AccountDescription

where a sample account would look like:
1 10-200AH Widget AH Purchase

AccountID would serve as a foreign key in other tables as needed.

Jim, if you post a description of the process you are trying to recprd in
your database and the relevant tables with their fields, I can help you much
more.

Steve



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

Thanks.


On Wed, 4 Mar 2009 17:36:44 -0500, "Steve"
wrote:

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



"Jim" wrote in message
. ..
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

Thanks, but think I got it figured out now.

Jim


On Wed, 4 Mar 2009 20:50:13 -0500, "Steve"
wrote:

No, I'm suggesting a table that looks like:
TblAccount
AccountID
AccountNumber
AccountDescription

where a sample account would look like:
1 10-200AH Widget AH Purchase

AccountID would serve as a foreign key in other tables as needed.

Jim, if you post a description of the process you are trying to recprd in
your database and the relevant tables with their fields, I can help you much
more.

Steve



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

Thanks.


On Wed, 4 Mar 2009 17:36:44 -0500, "Steve"
wrote:

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



"Jim" wrote in message
...
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

On Wed, 04 Mar 2009 10:28:47 -0600, Michael Gramelspacher
wrote:

On Wed, 04 Mar 2009 08:24:25 -0700, Jim wrote:

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.


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

On Mon, 09 Mar 2009 11:05:36 -0600, Jim wrote:

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

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.

On Mon, 09 Mar 2009 15:08:38 -0500, Michael Gramelspacher
wrote:

On Mon, 09 Mar 2009 11:05:36 -0600, Jim wrote:

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

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