Update data from two tables

[peshonzi]

Hello, it's my first post



I have a problem - I can't build a proper criteria, probably...

I have two tables.

The first one is the general, where I store the data, and the second one is the table from which I lookup...

The [General] table contains:
-1 [Location]
-2 [Distance]

The [Second] table:
-1 [Location*]
-2 [Distance*]

So I lookup a [Location] from [Location*] and I want to creat an update query that will type the [Distance] from [Distance*] by choosing the right Location.

I will show you what I did:

-Field- [Distance]
-Table- [General]
-UpdateTo- [Second]![Distance*]
-Criteria- [General]![Location]

The tables have reletaionship, and the related fields ([Location] and [Location*]) are with same type and options. But there is a mistake, because it does not do it...

PLEASE HELP

[orange]

What does distance mean when you associate it with a single Location?

What do your tables represent?

[peshonzi]

Hi, orange!

Well, I work in a company that makes concrete. So we send trucks to different [Locations] which are several miles from the factory([Distance]). So I have to type lots of [Location]-s with the proper [Distance]-s to those Locations

I have created a form by which I fill the [General]-table, using the data from the [Second]-table. So, I want to create a query, that types the miles when I input the location!

[orange]

So the distance is the distance from the Factory to the Site Location.

How do you determine the Distance?
What about other factors, such as Amount of Concrete; Type of Concrete; CurrentCost of Concrete; Customer; etc

There are free data models at http://www.databaseanswers.org/data_...ucts/index.htm

[peshonzi]

Look, I've already done the rest of it.

The only problem is - HOW TO CREATE AN UPDATE QUERY, which is going to input the kilometers, by reading the location I've selected in [General], and using the same location in [Second] to find out the kilometers.

[orange]

Try this code as your base. If you are getting a value from a selection on a form, you'll have to adjust accordingly.

UPDATE GENERAL INNER JOIN SECOND ON
General.Location = Second.Location
SET General.Distance = Second.Distance;

EDIT:
To remove notifications, if that is an issue, you can set up an action query using

db.execute sqlString,dbFailOnError

where sqlString is a variable whose value is the SQL of the Update query presented previously.

Dim sqlString as string
sqlString = "UPDATE GENERAL INNER JOIN SECOND ON " _
& "General.Location = Second.Location " _
& " SET General.Distance = Second.Distance;"
db.execute sqlString,dbFailOnError

[peshonzi]

thanks a lot