database design/Access 2000

[rpj]

I am endeavoring to design a database for a SMALL medical clinic locatd on our little island.

I need to know how to set up a primary key that will have the following:

two digit currentyear "-"autonumber. Is this possible? Also can the design of a primary key be used on other tablesin the same database(i.e.) date and auto number combined as the primary key? or a PHN number(personal health number0 and auto number in combination??

[Paul Komski]

I think the easiest way to go would be to create a two-field primary key.

Set up a new table with an ID field set to autonumber and set this as the primary key.

Set up a second field called, say year, and then set its default value to, say 2004.

Then, in design view, from the View Menu open Indexes. There should already be a Primary key with the ID field set there. Under the ID field open the drop down box and select the year field (and you should now see a second key appear at the very left of the dialog box).

You can do something like this with as many tables as you like. You can thereafter use both fields (or just the autonumber field, since it will always be unique on its own) to link tables with referential integrity set so that the relevant values in other tables get updated "automatically" - though its not totally automatic unless the queries (or forms/subforms) are set up correctly.