matching portions of field

D

danthrom

I have a field with two distinict parts, a client alias and a file number,
together these two make up a field--case number-- that a the primary field
and links to several other tables. In a form, I want to link up the first
part of the field "client alias" with the client's name, so the user does not
have to choose from a combo box. Is there a way to do this?
 
T

tina

recommend you store the two values (client alias and file number) in two
fields, not one, to stay in line with normalization principles. you can use
the two fields together as a combination primary key for the table if you
wish. with the client alias in its' own field where it belongs, it should be
much easier for you to utilize that value independently, where you need to.

hth
 
D

danthrom

I've tried it that way, and yes it makes accessing client information easier.
basically I have four tables, one for client information, one for file
information, one for storage information (tracks where we put files after
they have been closed) and a fourth table which is file details in relation
to storage information.

the file code (combination of client alias and file number) is actually the
"primary key" for the whole table, it is what the office uses in reference.

My problem has come with how to store information in the storage table,
since the boxes can have many differant files, or one file can have many
differant boxes.

with a client table, primary key client alias, and a file table, primary key
file number and foreign key client alias, how do I include a storage table
with primary key box number? (the file number can only be identified in
reference to the client alias, it is not unique by itself)

thanks,

danthrom
 
T

tina

i don't really get what you're asking. you can create a multi-field primary
key for a table, as i said. and a multi-field primary key can include a
foreign key field from another table, if that suits your purpose. if you
don't want to have to include multiple fields in a table as a foreign key
from another table, then use an Autonumber as a surrogate primary key,
instead of a multi-field primary key.

also, don't confuse how the *user* identifies a specific record with how
*Access* identifies a specific record. you can use an Autonumber primary key
for a table (which the user should NEVER see), which will then be the
foreign key for related child tables. and you can use any other field or
combination of fields as the identifiers that the *user* will review to
select a specific record. if you need to ensure unique-ness in that field or
fields, you can set a unique index in the table - which will not affect the
Autonumber primary key field at all.

hth
 
Top