table design with external data

[kjbirnie]

I am using an external data source for client names and type. In my previous version of this database I have linked the information which has worked fine as the external data is updated daily. Now I have need to add another table for type which will include a type ID to be linked in other tables as well. The linked table already has this field from the external database, but not in ID form. What is the most efficient way to do this? Should I know be importing the external data, make a query that converts the field to a code, or can it be done in VBA code? I will need to match up the Client and Staff by Type

tblClient - ClientID
ClientName
ClientType

tblType - TypeID
Type

tblStaff - StaffID
StaffFname
StaffLname
TypeID

tblInteraction - InteractionID
ClientID
StaffID

Any help is greatly appreciated!

 

[johnherman]

It can be done using VB during loads or by stored procedures after the load. Alot depends on how you want the process to work and how you have defined columns in the tables (not null, lookup, etc).

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright