473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update column with content from another table based on criteria

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!
Aug 26 '09 #1
8 5959
FishVal
2,653 Expert 2GB
@Fluffygoldfish
Do you mean now table structure is normalized?

Kind regards,
Fish ... simply Fish.
Aug 27 '09 #2
Hi Fish :)

Umm yes I think so, fairly new to access!
Aug 27 '09 #3
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!
Aug 27 '09 #5
ajalwaysus
266 Expert 100+
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
Aug 27 '09 #6
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!
Sep 1 '09 #7
FishVal
2,653 Expert 2GB
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?
Sep 1 '09 #8
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)
Sep 1 '09 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
3
by: rrh | last post by:
I am trying to update a field in one table with data from another table. The problem I'm running into is I need to base the update on a range of data in the 2nd table. Table 1 has: date field...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
1
by: meyvn77 | last post by:
I'm new to adp w/ sql server but I have to use it on a project i'm doing... One of the MUSTS for this project is the ability to update a 00 - 09 text value with the appropriate text description...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
6
by: Steve | last post by:
I realize that this probably isn't a best practice, but I'm working with legacy code that has a query stored in one column of a table. Because the queries vary, the JSP page that selects a query...
3
by: Brad Baker | last post by:
I have a formview with a datasource that contains a select and update command. The select statement works fine but the update command doesn't seem to be working. After some troubleshooting I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.