Combine 3 tables in query

[faca]

I want to combine 3 tables in query..


Tables are:

Customer (CustomerID, FirstName, LastName)
Car (CarID, CustomerID, Make, Model)
Service (ServiceID, CustomerID, Kilometers, DateOfService)



If i put CarID in table Service it doesn't work (can't create new record) neither doesn't work if i enter CarID in query..

How to solve this?

I want this to be joined beacuse when ill create new service i'll pick customer and than i can choose related car (if customer has more than 1)

Thanks

[alansidman]

Maybe

SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName, Car.CarID, Car.Make, Car.Model, Service.Kilometers, Service.DateOfService
FROM (Customer INNER JOIN Car ON Customer.CustomerID = Car.CustomerID) INNER JOIN Service ON Car.CarID = Service.CarID;

[CJ_London]

queries with multiple tables cannot be edited or have new records appended - although if you change the dataset type to inconsistent updates, you might be able to, but I wouldn't recommend it.

instead you use a form, the rule being one table, one form. have your customer table on the main form and the other two tables in subforms.

Your service table does not look right, think you need carid rather than customerid - you can get to customerid via the car table