Make one field = another in Access.
June 23, 2006 10:01 AM Subscribe
I have two tables in Access and this is what I want to do:
I want field 3 in table 2 to equal field 1 in table 1 (so I only input the data into table 1 field 1 and it appears in table 2 field 3). I know how to do this in Excel, and I know I can do it in Access, I just can't for the life of me figure out how.
I know I have to set up a relationship between the tables (done), but I don't know what else to do.
I'm pretty good at Access, but relationships and stuff like this always give me a little more trouble than they really should.
Thanks!
I want field 3 in table 2 to equal field 1 in table 1 (so I only input the data into table 1 field 1 and it appears in table 2 field 3). I know how to do this in Excel, and I know I can do it in Access, I just can't for the life of me figure out how.
I know I have to set up a relationship between the tables (done), but I don't know what else to do.
I'm pretty good at Access, but relationships and stuff like this always give me a little more trouble than they really should.
Thanks!
The standard approach would be to normalize your database rather than copy data across - you could drop field 3 from table 2, rename table 2, and create a view with table 2's old name that joins tables 1 and 2 to give you the columns you want. Any query that uses table 2 won't know the difference.
posted by ny_scotsman at 10:24 AM on June 23, 2006
posted by ny_scotsman at 10:24 AM on June 23, 2006
sounds good scotsman, but i think this is even above my head (and i thought i knew access). what do you mean by 'a view'?
posted by misanthropicsarah at 12:07 PM on June 23, 2006
posted by misanthropicsarah at 12:07 PM on June 23, 2006
In Access I think you simulate database views with queries. So, basically, the sequence here should be, given tables called Table1 and Table2:
- Delete Field3 from Table2
- Rename Table2 (let's call it Table2a)
- Create a query called Table2 that contains all the fields from Table2a plus Field1 from Table1 (this will be possible if there is a logical way to join the tables)
Anyplace you currently reference Table2 should now reference the query called Table2 and give you all the necessary data without duplicating anything (I think).
posted by uncleozzy at 12:26 PM on June 23, 2006
- Delete Field3 from Table2
- Rename Table2 (let's call it Table2a)
- Create a query called Table2 that contains all the fields from Table2a plus Field1 from Table1 (this will be possible if there is a logical way to join the tables)
Anyplace you currently reference Table2 should now reference the query called Table2 and give you all the necessary data without duplicating anything (I think).
posted by uncleozzy at 12:26 PM on June 23, 2006
Congratulations, you've discovered one of the fundamental principles of database design!
You never, ever, ever, ever want to duplicate data, between tables.
If you have two tables, one of which need to have information from the other table, you use something called a foreign key.
Say, for example, you have one table of employees, and each employee is in a department, and each department on on a specific floor, and you want to store al that data.
The first thing you do, is create your two tables. One with the employee columns -- First Name, Last Name, etc..
The second table has the department information -- say Name and Floor
In each table you create a column called id. Set that to be an auto-incrementing integer. Then right click on that field and choose 'primary key' That will be the number that your database will use to refer to each row in the database.
Now, in the employee id, you create a field called 'department_id' and you create a relationship using the relationship chart connecting 'department_id" in the employee chart, with 'id' in the department chart.
What you've now done is create a one-to-many relationship between departments and employees (each department can have many employees but each employee is only in one department).
Now, you can use all of access wizards and queries to join those two tables in interesting ways.
If you want more information, look up Database Normalization. It's a very difficult subject to get your head around at first, but once it clicks, it will make your life so much easier.
posted by empath at 12:33 PM on June 23, 2006
You never, ever, ever, ever want to duplicate data, between tables.
If you have two tables, one of which need to have information from the other table, you use something called a foreign key.
Say, for example, you have one table of employees, and each employee is in a department, and each department on on a specific floor, and you want to store al that data.
The first thing you do, is create your two tables. One with the employee columns -- First Name, Last Name, etc..
The second table has the department information -- say Name and Floor
In each table you create a column called id. Set that to be an auto-incrementing integer. Then right click on that field and choose 'primary key' That will be the number that your database will use to refer to each row in the database.
Now, in the employee id, you create a field called 'department_id' and you create a relationship using the relationship chart connecting 'department_id" in the employee chart, with 'id' in the department chart.
What you've now done is create a one-to-many relationship between departments and employees (each department can have many employees but each employee is only in one department).
Now, you can use all of access wizards and queries to join those two tables in interesting ways.
If you want more information, look up Database Normalization. It's a very difficult subject to get your head around at first, but once it clicks, it will make your life so much easier.
posted by empath at 12:33 PM on June 23, 2006
Ugh, it's hard to proof-read in this tiny box (particularly when one is typing while on the phone). There are some mistakes in my answer, but I think you can get the idea.
Basically, you create a foreign key in one table to refer to the primary key in the other table, instead of duplicating data. Then you write a query to get the fields from the two tables.
posted by empath at 12:35 PM on June 23, 2006
Basically, you create a foreign key in one table to refer to the primary key in the other table, instead of duplicating data. Then you write a query to get the fields from the two tables.
posted by empath at 12:35 PM on June 23, 2006
Apparently my work PC does have Access installed, so if you can post descriptions (e.g. name, data type of each column) of your tables, I can tell you how to create the view (in Access, query) I'm talking about. empath describes it pretty well, but if you need a hand applying it to your particular case let me know.
neilkod : him. :)
posted by ny_scotsman at 12:48 PM on June 23, 2006
neilkod : him. :)
posted by ny_scotsman at 12:48 PM on June 23, 2006
This thread is closed to new comments.
UPDATE (table2 INNER JOIN table1 ON table1.key=table2.key) SET table2.field3 = table1.field1
posted by Doofus Magoo at 10:12 AM on June 23, 2006