A simple lookup Puzzle :

[nifty]

I have what appears to be a simple puzzle. This is a simplified version of the real database. The purpose of the database is to act as a simple calculator - in this case to find the cost of a journey in a specified car to a specified city.
There are 4 tables.
Table1(input Data)with 2 fields: Car and City.
Table2(City),with fields City and Distance where city is the primary key.
Table3(mpg), with car and mpg where car is the primary key.

A query is written that when the user enters a "car" and "city" in Table 1 it looks up the distance to the city and the miles per gallon for the car. The Query is:
SELECT [Table1(Input data)].City, [Table2(City)].Distance, [Table1(Input data)].Car, [Table3(mpg)].mpg
FROM ([Table2(City)] INNER JOIN [Table1(Input data)] ON [Table2(City)].City = [Table1(Input data)].City) INNER JOIN [Table3(mpg)] ON [Table1(Input data)].Car = [Table3(mpg)].Car;

This works perfectly.
Table 4(cost) : contains 3 fields Distance, mpg, and cost with both distance and mpg as primary keys. How can Write a query that when the user enters the city and car, the distance and mpg are looked in table 2 and 3 and the cost is looked up in table 4. I have tried the following without success.

SELECT [Table1 Query].City, [Table1 Query].Distance, [Table1 Query].mpg, [Table1 Query].Car, [Table4(Cost)].[Journey's cost]
FROM [Table1 Query] INNER JOIN [Table4(Cost)] ON ([Table1 Query].Distance = [Table4(Cost)].Distance) AND ([Table1 Query].Car = [Table4(Cost)].MPG);

Many thanks for trying,
Nifty

 

[SQL_Hell]

([Table2(City)].Distance = [Table2(Cost)].Distance);

In the last field shoudn't it be Table 4?

INNER JOIN [Table4(Cost)]
[Table4(Cost)].Distance);

 

[nifty]

Thank you for pointing out table labelling error. I have corrected the error in the original script. Any idea how to get around this problem. The problem always arises when information from 2 separate tables is needed to lookup a third table in a query. This is essentially the hub of the puzzle. It can occur so often in database design that I would be suprised if it is not amenable to very simple solution. I constructed the posted database as a simple example of the problem. The solution must encompass the user being able to enter the Car and City and using the lookup tables the query should return the Journey cost.

Many thanks in advance for any attempts.
Nifty