Query returning Duplicates

[Archie1]

Hello I am very new to access and this is my first database. My database is vehicle management for a mechanic who wants to be able to store all his vehicle data, customer records and raise invoices and keep that invoice data for future reference
I have 3 main 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
In a scenario where a customer changes their vehicle, I have their "previous" vehicle marked as "inactive" I then go on to enter their "new" vehicle details which appeared to be working ok. I now need a query that will show all invoice data inclusive of "inactive" vehicles, customer info and vehicle info. However, for 1 particular customer that has a inactive and active vehicle with an invoice for both, it is duplicating the Invoice number which is an autonumber and set to "no duplicates" Any ideas? Thanks in advance

 

[jdraw]

Do a compact and repair on your database. Then create a zip and attach it to a post.
You can attach a zip even if you do not yet have 10 posts.

You can attach the zip by clicking on the Go Advanced button.

 

[Archie1]

Thanks I have done that. The query in question is QAllInvoices. You will see that Invoice number AM004 is duplicated. Its the same customer who has had a previous car with a previous invoice (now deleted). Thanks very much for looking. Much appreciated

 

[plog]

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

That's your issue. You shouldn't have circuitous relationships. Tables should only be accessible to other tables via one path. As you have it now you can go from Customers to Invoices directly and indirectly via Vehicles, that is incorrect.

Invoices should not be directly linked to Customers. They should be linked to Vehicles which in turn are linked to Customers...

Unless of course an Invoice can be for multiple vehicles. Is that possible?

 

[Archie1]

Hi One vehicle can have many invoices but a single invoice would be just the one vehicle. Thanks I will give that a go