duplicating certain fields

[Guest]

I have a database with several form all linked by clients last name, first
name, and ID number. these are all text fields. each client has records in
several forms/tables.

i want to be able to query my entire database to find all of the
forms/tables that the client has a record in. then i want to be able to
duplicate selected records (ie. duplicate there record in table A, B but not
C). finally, i need to duplicate while chaning the primary key (which is
just a number 1, 2, etc.). any ideas would be greatly appreciated.

thank you
russ

 

[Allen Browne]

Russ, if you duplicate the client's information (name, address, ...) in lots
of different tables, it sounds like an absolute nightmare to maintain.

Instead, just create a Client table, with a ClientID primary key
(AutoNumber), and other fields for Surname, FirstName, Address, City, Zip,
Phone, BirthDate, etc. Then in your other tables, don't store anything
except a ClientID (Number field). By creating a relationship back to the
Client table (Tools | Relationships) you can get at all this information
from other tables.