Concatinating one PK from one tbl & autonumber for a PK in a 2nd t

[Richard]

I have many tables based on two fields in two different tables. Rather than
have both PKs in every table, I would like to have a PK from one table
(tblStyle SK (StyleKey) 1...50) be in combination with an autonumber giving
me a unique ID based on the two so that it looks like this:

tblDesign Primary Key = (SK)37+autonumber = 37405

If this is possible in Access, would you please explain how I would go about
doing this? Thank you

 

[Douglas J. Steele]

What's the matter with having two separate fields? That's the way it should
be done. What you're proposing would actually be a violation of database
normalization principles, one of which states that each field should be
atomic (you're trying to put two pieces of information into one field).

If you want 37405 displayed on forms or reports, you can concatenate the two
fields. Store them separately though.

 

[Tony Toews [MVP]]

I have many tables based on two fields in two different tables. Rather than
have both PKs in every table, I would like to have a PK from one table
(tblStyle SK (StyleKey) 1...50) be in combination with an autonumber giving
me a unique ID based on the two so that it looks like this:

tblDesign Primary Key = (SK)37+autonumber = 37405

What happens if you have (SK)3 and autonumber 7405?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Richard]

Thanks for your reply Tony.

Yes I had thought of that too, and decided that my next question, IF the
concatenation above was possible was to ask if it was possible to have a
rigid 10 digit number, or concatenate with a letter or three 9s between the
two. First things first. R-

 

[Richard]

Thanks for your reply Douglas.

It appears that we have a misunderstanding. First, this is not for use in
forms and reports. Second, repeating the same TWO fields in all my tables
would seem to me to be against normalization. I could set up a third table
that has three fields - DK, SK, and UniqueID. I just wanted to get there in
the second table. After all, we don't use a separate table for the area code
of a phone number. (Access actually did that once for me when I got a list
of phone numbers from an Excel file.)

Say, I have 20 aux. tables all of which are dependent/related to the DK/SK
combination (the DKs come in many SKs). If I have to list both fields in
each aux. table in order to insure uniqueness, I will have many SKs in each,
which I see as against normalization. Having the DK with an SK "prefix"
would also be a good visual check that would help the user to know the style
at a glance, much as seeing the area code 202 tells you it is Washington, D.C.

Perhaps it is not a good idea, and my third table above is better, but the
issue becomes more complex once I send replicas out because all those tables.
R-

 

[Sylvain Lafontaine]

From what I understand from your description, this seems to me as a very bad
idea. By generating your own *autonumber*, it's very easy to implement
something like that (using 0 to padd the low order key to a constant number
of digit). However, when you will try to join your two tables into a query,
you will be hurt be a *very* severe performance problem because you will
have to decompose your composite key into its elements and no index can
manage that in an effective way.

The use of a third table (DK, SK and UniqueID) could possibly alleviate this
problem but still, you sentence « Second, repeating the same TWO fields in
all my tables would seem to me to be against normalization » suggest that
you might have a design problem here: if only one combination of DK and SK
is permissible for each value of DK and SK then one of these values is
superfluous and the second table should use DK instead of SK as its primary
key. If other combinations are possible, then goodbye performance and
welcome slowness.

 

[Douglas J. Steele]

There's nothing wrong with the primary key being more than one field if
that's what it takes to guarantee uniqueness. You can have up to ten
separate fields in an index.

 

[Richard]

Thanks Sylvain for your reply.

I have indeed started over in the design process, and your comment about
"one of these values is superfluous" is close on. Part of my problem is a
lack of understanding (plain ignorance) about the process of replication, and
what I was trying to do here, is probably superfluous because replication I
think, takes care of the "security" I was trying to build in. Hope that
makes sense.
In any case, I need to learn about that process before proceeding with the
design.

Once again, thank you and all the others for your input. Working alone with
no one, who understands what you are doing, to run things by to get a second
opinion is mind-boggling, So, having you "experts" willing to share some of
your knowledge is something to be thankful for this coming Thanksgiving. I
will raise a toast to you all that day. R-
: