Key Field Links Please Help

L

Lin

Greetings and thanks.

I've been working on this forever, it seems. Just about there as far as
design.

Here's my dilema.

Parent file and number okay. Children use the same number, however we asign
a letter to indicate which child is admitted.

i.e: Mother: ann smith 12345
child: john smith 12345A
bob smith 12345B

My questions then.
1. Format for number, if I use a number type for field I can't key in the
letter (suffix). If I use text it works okay, except I believe that these
numbers can climb into 10 to 12 digits eventually. As you can guess this is
a monster and I have to allow for future expansion.

2. How to transfer old tables and have aligned properly.
Currently we are into 6 digits. The old database had 5 digits.
I would like for example the child of 12354A to read 012345A
again is the questioin of formatting, text to number.

3. Lastly, Will I still be able to link Mother to child?

Forever grateful.

PS: What are phantom fields and are they part of Access. My CFO asked me
to build in a few of these fields.
 
K

KARL DEWEY

Put the suffix (A, B, etc) in a separate field. Then your numbers can grow -
there is no need to add zeros in front. If you want consistent lenght
numbers the do an update query to add to the existing numbers like -- 12345
add 1000000 to get 1012345.
 
L

Lin

Thanks Karl

One question though, If i have two separate fields, how can I set the key
fields on both so that the number and the suffix are not repeated?

Can it be done.
 
J

John Vinson

Thanks Karl

One question though, If i have two separate fields, how can I set the key
fields on both so that the number and the suffix are not repeated?

Can it be done.
Click to expand...

Very simply:

Open the table in design view.
Ctrl-click both fields so that they're both highlighted.
Click the Key icon.

Your Primary Key will now consist of the two fields; either one can be
duplicated (that is, you can have a 318A and a 412A; or a 318A and a
318B) but the combination cannot.

One downside: you cannot leave the suffix NULL. You'll need to either
give every record a suffix, or (and I don't like this idea much) set
the suffix field's Allow Zero Length property to True, and its Default
to "".


John W. Vinson[MVP]
 
Top