Creating a Primary Key from Other fields

[Lhsmith]

How do I create a PK that is portion of other fields within the db. For
Example,

Sample DB tables
table_ 1
field_1(PK)
field_2 (alpha)
field_3 Numeric

table_2
field_1 (PK) First three characters of table_1, field_1 + last three
characters of field_2 table_1.

 

[KARL DEWEY]

Try main form - subform. In main form have the table_1 field_1(PK).
In the subform have table_1 field_1(FK). The next field from table_2 (text
field for 6 characters) default the textbox in the form to
=Left([table_1].[field_1],3) & Right([table_1].[field_2],3)

 

[Tim Ferguson]

table_2
field_1 (PK) First three characters of table_1, field_1 + last three
characters of field_2 table_1.

I am having difficulty in imagining why you would want to do this: in this
simple case, you can replace table_2 altogether with a query that looks
like

SELECT LEFT(Field_1, 3) & RIGHT(Field_2, 3) AS MyNewString
FROM table_1
ORDER BY Field_1

Perhaps you would be able to describe what you are trying to achieve,
because I'd bet good money that this is not the way to get there.

Best wishes


Tim F

 

[Lhsmith]

Hi Tim,

I do some direct marketing and am building a database to assist in that
effort. my DB consists of 7 tables and 6 have many-to-many relationships.
Some PKs i get from info I am provided. Others use autonumber. some I wanted
to create so they had relevance to the other tables. I didn't want to use
autonumber on 5 different tables within one DB if there was another way to
create PKs.

To give you more detail, I send letters to foreclosure property owners
attempting to buy their house. I get a list of names every day for new
contacts. I am looking to better automate the direct mail and tracking
process.

my tables are
DATES
case_number (PK - given)
list_date
critical_date

ID_NUMBER(many-to-many with DATES and CLIENT_NAME)
case_number
client_number
Clientfile_number(PK)

CLIENT_NAME(many-to many with ID_NUMBER and CLIENT_ADDRESS)
first_name
last_name
client_number(pk)

CLIENTADDRESS_ID(many-to-many with CLIENT_NAME and ADDRESS)
clientaddress_ID (pk)
client_number
parcel_number

ADDRESS (one-to-many with CLIENTADDRESS_ID and on-to-one with PROPERTY_DETAILS
parcel_number (pk - given)
Address_type
Street_no.
street_name
unit_number
zip_code

ZIP (haven't quite figured out how I am going to handle this but it's here)
city
state
zip

PROPERTY_DETAILS (one-to-one with ADDRESS)
parcel_number (pk - given)
40 other fields

I think I have normalized the DB and now I am preparing sample data but i
was hoping to create some PKs as opposed to using autonumber on three
separate tables.

Any suggestions?

 

[Tim Ferguson]

I think I have normalized the DB and now I am preparing sample data
but i was hoping to create some PKs as opposed to using autonumber on
three separate tables.

Any suggestions?

No not really... I am happy to take your word for it that this is
normalised, but I am completely blown away by your naming strategy. For
example:

ID_NUMBER(many-to-many with DATES and CLIENT_NAME)
case_number
client_number
Clientfile_number(PK)


To me, an entity called ID_NUMBER would contain a list of ID numbers...
not, in most cases, a very helpful idea. Still, you then say it's "many-
to-many" with two other tables, which I guess means you are referring to
a junction table, and again I am guessing that Case_Number is a FK
referencing DATES (but then again, DATES looks more like a table of Cases
so that kind of makes sense...). Ditto Client_Number referencing a table
of Client_Names.

All that said, I cannot see the point of having the ClientFile_Number --
that is to say, it would be a good name for the id of a ClientFiles table
but I don't get the impression that this table is it. If this is a
junction table, then it's perfectly in order to make the PK (Case_Number,
Client_Number) and leave it at that. Then again, I guess you already know
that so perhaps I have got the wrong end of the stick.

Look: I feel a bit horrid criticising what is, after all, a personal
style; and I cannot say that you are actually doing anything wrong. I
would say, though, from the long view, that you would be doing a favour
to yourself, and especially those who follow you, by rationalising this
sort of thing. It may make perfect sense to you now, but just try
debugging this in nine months time, when you have been doing completely
other stuff in the meanwhile.

Best wishes anyway


Tim F

 

[Lhsmith]

Tim, Let's see if I can respond to your questions. First, my naming
convention may be a bit off as i am new to DB design and Access. ID_NUMBER
is a junction table. Both case_number and client_number are PKs (or FKs) on
other tables.

I am not sure what you're referring to when you ask me to rationalize but it
is possible that a name associated with a property will show up more than
once (some folks enter the foreclosure process - fix there current situation
and then find themselves in the same situation 6 months later). In that
instance, all information in the database would be the same EXCEPT for
case_number. therefore clientfile_number would allow that to happen because
it would be the only unique character between the two instances. Does that
make sense?

I am the first to admit that I may be in over my head but I was hoping that
between my limited knowledge, some help from friends, and this forum, that I
would be able to construct what it is I am looking for.

All suggestions welcome.

 

[Tim Ferguson]

I am not sure what you're referring to when you ask me to rationalize
but it is possible that a name associated with a property will show up
more than once

Okay: a table of Vehicles will have a field called NumberOfSeats, and so
will a table of MeetingRooms... generally, though, if two things are the
same then they should be in the same table.

(some folks enter the foreclosure process - fix there
current situation and then find themselves in the same situation 6
months later).

Surely that means two records in the same table then -- or, rather, one
record in the Folk table and two in the Foreclosures table.

In that instance, all information in the database
would be the same EXCEPT for case_number.

If you are really not recording anything about a case except its number;
but that seems just a little bit rudimentary.

As I said before, I am not at all sure that what you have done already is
wrong: it's just that I cannot imagine how you might order things better
because I can't see where you are now. Think of it this way:

TABLES ARE NOUNS -- sometimes abstract ones, but typical entity names
might be something like People, Cases, CourtHearings, Allocations,
LettersSent, and so on. Some tables have special roles like "junction"
tables, and it might make sense to use verbs for these: IsAllocatedTo,
IsAPartyTo, SubmittedSignatureOn, etc.

COLUMNS ARE ATTRIBUTES -- and are often adjectival phrases: Colour,
NumberOfSeats, FirstName. Some columns are special because they indicate
where other information can be held (i.e. foreign keys) but the name
should still indicate the role the field plays in this table: BuildingNum
is meaningless, IsResidentAt is obvious. Sometimes it's helpful again to
use verbs for foreign keys.

The short answer is that your naming scheme is your business not mine! To
go back to your original question (and I don't really see how it links in
to the subsequent description), why do you really want to create a field
that is made up of bits of other fields?

B Wishes


Tim F