Query problem

[Traceyann1964]

Hi I am still struggling with my database. I have 3 tables. Customers, Vehicles and Invoicing.


My relationships are Customers to vehicles one to many and Customers to Invoicing one to many both linked via Customer ID. I then have a one to many relationship between vehicles and invoicing linked via vehicle reg.
What I would like is a query to look at all invoices but for the query to show the vehicle information tied to that invoice and possibly the customer that has or had that vehicle.

If I design a select query based on the invoicing table, it only shows invoices. If I add the vehicle table, it returns blank results. If I change the join, I get results but the vehicle fields are blank. Any ideas as to what I am doing wrong?

[ranman256]

tCustomers.custID -->tInvoices.custID--> tVeh.custID

use an outer join in case not all invoices have a vehicle.

[Traceyann1964]

Hi Thanks for the reply. That worked thank you. I do have another question. In a scenario where a customer changes their vehicle, I have a field in the vehicles table called "inactive" and tick the box to say yes that is a previous vehicle. I then enter a new vehicle for that customer. If I then raise an invoice (through a form) for the "new" vehicle, and go back into the invoicing table, the invoice for the "inactive" vehicle is showing with the new vehicle details ie Car registration plate when it should be against the "inactive" vehicle. Sorry if that sounds complicated