Access 2007 updating one table based on another table

[MCE]

Hi,



Im pulling my hair out, no matter what way i do it i cannot get one field in one table to update a field in another table.

I have followed microsofts step by step instructions and still does not work.

Here is a link for the instructions: http://office.microsoft.com/en-au/ac...010076527.aspx

Any help or advice would be much appreciated, losing my patience with it.....

[June7]

Post the SQL statement you attempted. Or provide db - follow instructions at bottom of my post.

[MCE]

Hi, thanks for your reply

I want the value in the location field in table1 to update the value in the location field in table2

this is the SQL command im using:

UPDATE Table1 INNER JOIN Table2 ON (Table2.Location = Table1.Location) AND (Table1.Location = Table2.Location) SET Table2.Location = [table1].[location];

If you need still need the db just let me know and ill follow your instructions and post it.

[June7]

Why the second join?

UPDATE Table1 INNER JOIN Table2 ON (Table2.Location = Table1.Location) SET Table2.Location = [table1].[location];

[MCE]

This SQL statement was created by access.

I tried without the second join and i still get "You are about to update 0 row(s)" ?????????????

[June7]

Access would create those joins only if they are established in the Relationships window.

Could try reversing the join tables

UPDATE Table2 INNER JOIN Table1 ...

Beyond that, have no idea why this fails.

[MCE]

Got it!!

According to the microsoft instructions i posted the link to above, I have to join the tables:

• If the tables aren't already joined, join them on the fields that have related information.

How?
In most cases, you want to bring together or perform an action on data from more than one table or query. For example, you might want to view a customer's information with the orders the customer placed. To see this information, you need data from the Customers and Orders tables.
Join different tables and queries

• In query Design view, drag a field from the field list for one table or query to the equivalent field (a field of the same or compatible data type containing similar data) in the field list for the other table or query.

With this type of join, Microsoft Access selects records from both tables or queries only when the values in the joined fields are equal.
Note Join numeric fields only if the FieldSize property settings for both are Byte, Integer, or Long Integer.

In some cases, you want to join two copies of the same table or query, called a self-join, that combines records from the same table when there are matching values in the joined fields. For example, say you have an Employees table in which the ReportsTo field for each employee's record displays his or her manager's ID instead of name. You could use a self-join to display the manager's name in each employee's record instead.

I removed the join in the relationships window and did not drag and drop the corresponding fields and Eureka it worked perfectly.

Thanks for all your help.