Importing data where foreign keys are involved (1 Viewer)

[David8]

Hello could anyone help me with my understanding here?

I have what I think is a normalised database that uses foreign keys quite a bit, as I believe (correct me if I'm wrong) is regarded as good practice.

For example if I had a 'contracts' table which refers to 'clients', then there would be a tblClients, where each client has a primary key. The tblContracts would then refer to the relevant client via that clients FK only which would be linked to the client's PK in tblClients via a one-to-many relationship.

If I need any user for any purpose to see contract related information that makes sense to a human being, I simply construct a query with the necessary relationships that will show client information alongside contract information by substituting tblContracts' client FK with required information from tblClients via the appropriate relationships.

I think that is reasonably basic stuff (hopefully correct practice!)

But what about when I come to import a block of new data that needs to go into tblContracts? I'm not going to be given a list of client keys (obviously) I'm going to given their real names.

MS Access has (in theory) all the information it needs (via the relationships) to substitute client IDs (keys) for their real names and thus slot these IDs into tblContracts with the new data as appropriate, but how do I make it do this? (I know it could kick out errors if there are any duplicate client names, but let's put that to one side for a moment).

This can't be a new problem, how should I deal with it?

 

[plog]

I would bring your new data into a new table, add a field to hold the foreign key, then set up queries to find those foreign keys. Once all the records have foreign keys, append the data to tblContracts.

I also guarantee its not going to be as straightforward as linking the names together (John, Jon, Jonathan, J.). And you will have some that aren't in tblClients which means you will have to set up a record for them. You'll be able to run queries for 90% of them, but I bet 10% will be a manual process.