Updating a table from another table 1

[1angryadmin]

I have a database that i inherited from 3 previous admins and to make a long story short....

I need the data from one coloumn in table A
to update the data in a certain column in table B

I would like to use ASP/SQL not access unless its just so much easier in access...

Please it is killing me...

 

[ptpaton]

An update query in access will take care of this for you...pretty easy too.

1. Pull the field from Table B down
2. In the UpdateTo row, select the field in table A
3. Establish some kind of criteria by pulling down another field from table B, and putting the corresponding field from table A as its criteria.

Should work..let me know.

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[1angryadmin]

ok i am getting close thanks to your help now the problem is


their are 2 criteria i want to set

1. is when something in table B column x = 1 then perform this update.

2. is that the name of the material in table A must match the name of the material in table B

thanks so much for your first tip i can tell that i am close to a resolution...

 

[ptpaton]

Ok,

For #1ull down the field from Table B, and type a "1" (In quotes) in its criteria

For #2: There's two things you can do:
1. Pull down the field from table B, and reference to the field in table A, such as
[Tables]![TableA]![YourField]
or
2. Add table A to the query window and create a join between the two fields of interest. Do that by dragging the field from one of the tables on top of the field in the other table, then click ok when the join type box pops up. This will ensure that the update query will only run on records that match at that field.

Make sure that the criteria for both fields are on the same line in the criteria section of the query, or you'll wind up with an "or" statement in the SQL instead of an "AND" statement.

I probably should have warned you earlier, but its always a good idea to backup your table before running an update query. If you do something wrong, you can seriously screw up your data. (Hope it's not too late to warn you!)

Let me know how that goes..


-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[1angryadmin]

Thanks so much you have led me to the promised land...

I have been studying SQL / RDBMS for like a year now and i find its the little things like this update query which can prove to be the most difficult to discover and the most satisfying to complete...

 

[ptpaton]

Glad I could help! The query builder in Access really simplifies the SQL process too. Don't worry though...I've been designing for 8 years and queries can still be a mystery to me...

Have fun!


-Patrick

Nine times out of ten, the simplest solution is the best one.