looking up tables with values from tables.

[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);

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 surprised 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. The key might be in the way the tables are linked.
Many thanks in advance for trying.

 

[ecniv]

tblCars
CarID - auto - pk
Manufacturer - text - 50 (make)
Model - text - 50
mpg - number - double

tblCities
CityID - auto - PK
CityName - text - 50
DistanceMiles - number - double

I would calc the cost... But if you need a look up table
tblCosts
TravelCostID - auto - pk
CarID - number
CityID - number
TravelCost - number - double

The cost would need to be specified per gallon as well as the users selection of car and city.

PARAMETERS UCarID Long, UCityID Long, CostPerGallon IEEEDouble;
SELECT tblCars.Manufacturer, tblCars.Model, tblCars.mpg, tblCities.CityName, tblCities.DistanceMiles, ([tblCities].[DistanceMiles]/[tblCars].[mpg])*[costpergallon] AS TravelCost, tblCars.CarID, tblCities.CityID
FROM tblCars, tblCities
WHERE (((tblCars.CarID)=[UcarID]) AND ((tblCities.CityID)=[UCityID]));

The above is a cartesian join though.

SELECT tblCars.Manufacturer, tblCars.Model, tblCars.mpg, tblCities.CityName, tblCities.DistanceMiles, tblTravelCost.TravelCost, tblTravelCost.CarID, tblTravelCost.CityID
FROM (tblTravelCost LEFT JOIN tblCars ON tblTravelCost.CarID = tblCars.CarID) LEFT JOIN tblCities ON tblTravelCost.CityID = tblCities.CityID
WHERE (((tblTravelCost.CarID)=1) AND ((tblTravelCost.CityID)=2));

The above is using a table of prices.

Both the above Sql statements were made in Access Query builder...

 

[nifty]

Thank you for your well thought out reply. As I am new to Access I will have to study it to see if it gets me around the problem. The car /cost problem is really a simplified version of a problem I keep running into and as such I am very interested in your solution.
Thanking you again.
Nifty.