help from africa

[dreamdelerium]

hey guys, im sure this question is somewhere in the forum but im currently serving in the peace corps in africa and it cost a lot to get on the internet here. heres my question. i have three tables in my database, each having a primary key that is autonumbered. i want to make sure each primary key is unique between tables. that is to say if i add a new record to table 1 i want to be sure that no record in any of the other tables share that same value. any ideas?

 

[CraigDolphin]

This seems very odd to me. The purpose of an autonumber primary key is to ensure uniqueness of records within a table. And while there is debate on the topic of meaningful vs meaningless primary keys, an autonumber pk is almost always treated as a meaningless pk so I can't imagine why you need to ensure that pks from three different tables do not have common vales.

I suspect that the question implies that you have larger db design concerns than you currently realize. If so, I would strongly suggest searching and reading up on table normalization before continuing your db design.

That said, one way to achieve what you say you want would be to create a new field in each table with a text datatype. Keep your original autonumber field but use this new field as your pk for the table. In your forms, use the on_dirty event to set the value of the new field to a table identifier of some kind concatenated together with the value of your autonumber field.

So, if your 3 tables had identifiers of 'A','B', and 'C' respectively, record 1 in each would be 'A1','B1', and 'C1' respectively.

 

[The_Doc_Man]

I'll make a stronger statement. The field you name cannot be an autonumber if it has sufficient "baggage" that it has to be unique vs. other tables. This would, in essence, violate the meaning (such as there is) for an autonumber.

To do this first suggests that your three tables should not be split if they have such an intimate interlocking relationship, and second suggests that this ID field has meaning beyond a simple unique record identifier.

I know it costs you to search the internet, so instead do yourself a big favor and use Access Help on NORMALIZATION.

If three tables "share" a key, normalization rules SUGGEST that they reference the same thing (mostly) and as such should be conjoined. If they DON'T reference the same "basic" thing, then they should not bear the relationship you suggest. Part of this is due to the general principle that when you are using an autonumber, it is because you do not have a meaningful candidate for PK. Therefore you use autonumber, which is a prime example of what we call a MEANINGLESS key.

Example: For a person table, social security number COULD have been a candidate for PK in a personnel table were it not illegal to use such personally identifying information in that exact way. (At least, illegal in the USA.) But an arbitrary number (translate: autonumber) works well, too.

 

[ajetrumpet]

I don't know why you need this dream, but if you do, you could always use a random autonumber (chances of duplicating though are pretty good with three tables). Or you could set the base number of your field for all three tables, if that helps....see the post in this thread to do that....http://www.access-programmers.co.uk/forums/showthread.php?t=134145

 

[CraigDolphin]

I'll make a stronger statement.

Gee Doc Man, guess I need to start doing more lingual push-ups

 

[dreamdelerium]

thanks guys. yeah, i have to admit im a bit lost here lol. im serving in the peace corps (a two year volunteer program working in botswana with hiv/aids). specifically im doing social work. one of the problems is that theres no effecient record keeping. so i designed this program to keeps records, do reports, updates stats, etc. i have a decent back ground in programing but my db design skils are lacking lol. any way , thanks for the help