Re: multiple field, primary key

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Wed, 04 Mar 2009 10:28:47 -0600, Michael Gramelspacher
<gramelsp@xxxxxxxx> wrote:

On Wed, 04 Mar 2009 08:24:25 -0700, Jim <dragonoff@xxxxxxxxx> 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.
.

Relevant Pages

  • Re: When does foreign key update?
    ... running a join query on ... On the form, on an insert, filling a value for field1 or field2 ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ...
    (microsoft.public.access.formscoding)
  • Re: When does foreign key update?
    ... running a join query on ... On the form, on an insert, filling a value for field1 or field2 ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ...
    (microsoft.public.access.formscoding)
  • Re: Selecting non-continuous records in a continuous form
    ... Then use a query or a filter to choose just the marked records. ... Field1: contains the primary key of the Source Table ... Field2: contains an identifier ...
    (microsoft.public.access.modulesdaovba)
  • Re: When does foreign key update?
    ... running a join query on ... On the form, on an insert, filling a value for field1 or field2 ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ...
    (microsoft.public.access.formscoding)
  • Re: When does foreign key update?
    ... running a join query on ... creates a primary key for tbl1. ... field3, the foreign key gets set to the primary automatically. ... do NOT enter any data into field3 or 4 (tbl2), ...
    (microsoft.public.access.formscoding)