Solved: Update table from another table?

[lienlee]

I have two tables. X and Y
X and Y have the same Columns: A and B

but lets just say..Table X has 1 more row of data in A and B. and Table Y only has data in B

How do i update table Y? without the data in B being updated in table X?

[Imdabaum]

If you have an ID establishing a relationship between X and Y then you would update where the IDS are the table row identifiers.

You could do UPDATE Y Set A = X.A WHERE Y.B = X.B, but Y.A will be updated everywhere X.B and Y.B are equal.

With a key you can identify which row is updated when X.B and Y.B are equal.
UPDATE Y Set A = X.A where Y.PKID(primary key id) = X.FKID(foreign key id). So even though X.B and Y.B have the same values in 100 rows, it only updates the row Y.PKID.

[lienlee]

If you have an ID establishing a relationship between X and Y then you would update where the IDS are the table row identifiers.

You could do UPDATE Y Set A = X.A WHERE Y.B = X.B, but Y.A will be updated everywhere X.B and Y.B are equal.

With a key you can identify which row is updated when X.B and Y.B are equal.
UPDATE Y Set A = X.A where Y.PKID(primary key id) = X.FKID(foreign key id). So even though X.B and Y.B have the same values in 100 rows, it only updates the row Y.PKID.

where would i use this update in access?
and these are my two tables..

thanks btw

[Imdabaum]

where would i use this update in access?
and these are my two tables..

thanks btw

That's a tough question. But ultimately it comes to a factor of need. When do you need the update to be performed. I have assigned Update queries on startup, or macros to be run at user determined times.

Since you are in 2007, you could assign it to a macro and place it in the ribbon so whenever you need it you can just go into the ribbon and click it.

You tell me when you need it, I'll tell you how to get it there.

Out of curiosity, these tables look identical, not just that they have some of the same data shared. They have all the same data. What is the difference in functionality and would one table not be able to perform the same functionality?

[lienlee]

That's a tough question. But ultimately it comes to a factor of need. When do you need the update to be performed. I have assigned Update queries on startup, or macros to be run at user determined times.

Since you are in 2007, you could assign it to a macro and place it in the ribbon so whenever you need it you can just go into the ribbon and click it.

You tell me when you need it, I'll tell you how to get it there.

Out of curiosity, these tables look identical, not just that they have some of the same data shared. They have all the same data. What is the difference in functionality and would one table not be able to perform the same functionality?

Hi, I actually got my problem solved..in a different way..total backwards lol

But um..they did actually have the same data..however..one of the table in the column is being used in a combobox....and the other table..i was planning to add more data into other columns. Once i added data into other columns..the column being used in the combobox would print out blanks. and i didnt want that.

So i figured i can make a duplicate table of it..add what i needed. and use that table instead of the table that is being used with the combobox..

sorry if that sounds confusing!
but thanks. i learned something new from what you said!

[Imdabaum]

Oh well then that's much easier to solve, I know you said you already solved it, but in your combo box properties, modify the data source to not include Null values or blank values.

example>:
SELECT Conceptual_Entity_ID, Data_Classification, Conceptual_Entity_Name WHERE (((Data_Classification) Not Null) AND Data_Classification <>"")

No need for duplicate tables and you don't see null values in your combo box (provided your query restricts the nulls from the right field).

Glad to help.