its me again

[dreamdelerium]

ok, let me clarify what im doing with the tables ( I thought it might be better to just start a new thread). i have three table, each holding records of three different types of clients (orphans, Home base care patients, and
destitutes). the tables are not linked and do not share data across tables (although they may share some similar fields types--first name, last name, etc-- each are to be treated independently of the other because for the most part their fields are different). i need a unique identifier for all three tables because i have a form
that searches all tables for particular client and returns that recordset for editing in another form. if table A, B, and C all have a recordset with a similar PK i would return 3 records, not the original one. i know i could do my search on PK, First Name, and Last Name but id rather not risk the chance of (even if it is remote) of all three criteria being created in two or more tables. i like the suggestion that CraigDolphin gave about matching the Autonumber with another identifier and using
that as my PK. one question, though: when i enter the recordset into the table how do i retrieve the Autonumber for that new record (to match with the new "table
identifier") if the recordset hasnt been created yet. the only thing i can think of doing is create the recordset, then go back and update the recordset with the new PK.
this then brings up another question ive been trying to figure out. how do i retrieve the PK field from the recordset and return it to my form to be used?

 

[Rich]

You don't need three separate tables, one will do, just add a field to define the status of the person

 

[Ian Mac]

Firstly, 'Its me again.' holds absolutely no useful information about the question. Please try to describe the problem in the subject.

OK, you say, 'each are to be treated independently of the other because for the most part their fields are different'
Can you please post what each table has as I'm leaning towards Rich with this one. I think you may only need one table.

Thanks

 

[CraigDolphin]

D's original thread was here http://www.access-programmers.co.uk/forums/showthread.php?t=134414. And since D mentioned my name here, I think I ought to respond.

I agree with the majority of posters who think you need to reconsider your design rather than implement my 'workaround' which was given only after advice to normalize your db.

So, perhaps a little example exercise will be more persuasive.

Consider one main 'client' table.

tClient
ClientID (Autonumber, PK)
ClientFirstName
ClientLastName
ClientBirthDate
ClientTypeID (FK)

you have identified three types of clients so we need another table to contain those types to avoid repeating groups in your Clients table.

tClientTypes
ClientTypeID (Autonumber; PK)
ClientType (Text) --> this field contains values 'Orphans', 'Home base care patients', and 'Destitutes'.

You link the PK of tClientTypes to the FK of tClients.

Now, you also have some fields that apply to orphans only, some to destitutes only, and some to home base care patients only. You might even have some fields common to two of the three client types. Let's call those fields 'parameters' for now. We need a list of parameters.

tParameters
ParameterID (Autonumber; PK)
ParameterName (Text)


We now need a way to store information about those parameters for each client.

tClientParameters
ClientParameterID (Autonmuber; PK)
ClientID (FK)
ParameterID (FK)
ClientParameterValue (Text)

You might further refine this model by specifying which ClientType(s) a Parameter applies to.

tClientTypeParameters
ClientTypeParameterID (Autonumber PK)
ClientTypeID (FK)
ParameterID (FK)

Such a table would assist you in deriving cascading combo box lists on forms etc.

In this system, you only ever have to refer to a client using ClientID. You can retrieve lists of clients belonging to a client type 1 (orphans) using Where [ClientTypeID] = 1 in a query of tClients.

Hope it helps. Others like DocMan might suggest a better data model. If they do, listen to them. If you don't do the table design right you will find everything that you want to do later will be vastly more difficult. What ought to be a simple task will become a horrible grind and you'll be back here looking for help more often than you like.