Update column with content from another table based on criteria

[Fluffygoldfish]

Hi

I am trying to tidy up an access database that has traditionally just been used as a spreadsheet. I have split up the information into different tables and need some help transferring information.

I now have two tables:

Client Info - Client ID (primary key), Name, DOB,
Client Address - Address Id (primary key), Client ID, Address 1, Address 2, etc

They are linked (client info) one to many (client address). The client id is a new column I have added in the Client Info table, to enable multiple addresses against a client. If i leave the Name and DOB, fields in the Client Address table for the meantime is there any way I can get access to find the corresponding details in the client Info table and automatically update the Client ID fields in the Address table?

Does this make sense? Or do I have to manually put the tables side by side and type in the ID numbers in the client address table to match the client info table?

Thanks!

[FishVal]

...

Client Info - Client ID (primary key), Name, DOB,
Client Address - Address Id (primary key), Client ID, Address 1, Address 2, etc

...

Do you mean now table structure is normalized?

Kind regards,
Fish ... simply Fish.

[Fluffygoldfish]

Hi Fish :)

Umm yes I think so, fairly new to access!

[FishVal]

Database Normalization and Table Structures
Wikipedia: Relational database

[Fluffygoldfish]

Thanks! Yes it is normalised.

What I am trying to achieve is:

If in Client Info table I had:
Joe Bloggs
Fred Smith
(each with an individual Client ID number)

And then in the address table I had 2 address records for Joe Bloggs and 1 for Fred Smith. I could link the two tables by having a Client ID column in the address table. I want the database to find all the addresses for Joe Bloggs (using name/dob) and assign them with his Client ID number from the Client Info table. Without having to do loads of manual entering of numbers.

It just needs to be a one off query for setting it up, as I have created a form so that any new addresses added will automatically link with Client Info table and hence client ID.

Thanks again!

[ajalwaysus]

Do you have a ClientID in your Client Info table? Why can't you join on the ClientID?

If I am way off, please post the table structures of each Client Info table and address table.

-AJ

[Fluffygoldfish]

Ah I think I've made it confusing by how the fields/columns are labelled!

Original Table: Name, DOB, House Name, Street Name, Post Code
So for everyone that had two or more addresses there would be two or more records in the table.

I split it so that:
Client Info Table: Client ID (primary key), Name, DOB
Client Address Table: Address ID (p key), Client ID, House Name, Street Name, Post Code.

I have linked the tables by Client ID one to many. My problem is I now have a table with a lot of addresses in with the Client ID field blank, because the data was already in there.

If I leave the name / DOB in the address table temporarily, is there any way to get access to automatically search for matching records in Client Info and update the Client ID in the address table?

Thanks again!

[FishVal]

You could join both tables on equal name/DOB and update FK field with value from correspondent PK field.
But, just out of curiosity, did you try to run Access built-in table analyzer?

[Fluffygoldfish]

Thank you so so much for your help. I'd almost given up on it then, but managed to get it to work. Fantastic! :o)