primary/foreign keys

[pdebaets]

Using this setup it appears the service table is a dead end as far as display the services preformed.

You can create a join query that joins Customers with Vehicles with VehicleServices with Services.

[pdebaets]

Looks like I now have what I think I should see. I now have three tables. Cust, Servies and Vehicle.

Cust Table: PK CustomerID

Vehicle Table: PK VehicleID
FK CustomerID

Services Table: PK ServiceID
FK VehicleID

With this schema, a Service can be performed on one and only one Vehicle. I don't think you want that, but I could be mistaken.

I presume that your services are something like "Oil Change", "Tune-Up", "Tire Rotation" etc..., correct? If so, then that calls for a separate Services table.

[pdebaets]

Doing away with the VehicleServices Table (not sure why/what it actually did)

The VehicleServices table links Vehicles with the Services that have been performed on each vehicle. It also links Services with all the Vehicles that have received that service. It is an essential part of a many-to-many relationship.

[st1300]

One customer, many vehicles (working)
One vehicle, service done (not working)

See relationship capture.

[pdebaets]

Go back in this thread to where I said: "The PK's and FK's look OK :-)"

You had the PK's and FK's right back then.

Remove "VehicleID" from the Services table. A particular Vehicle is not an attribute of a Service.

It looks like you have a table field for each service you can perform. that's not the way to do it. The Services table should look like this:

Table: Services
PK: ServiceID
ServiceDescription
Frequency

Then create a form for Services. Input the different services as different records in the Services table using your Services form.

[Rainlover]

Delete Relationship between Services and Vehicle.

Add Relationship between VehicleServices and Vehicle.

Delete VehicleID from Table Services.

So therefore you have a Customer Joined to many Vehicles, Joined to Many VehicleServices joined to Many Services.

I would also prefix all Tables with tbl. EG tblVehicles. Also note the Plural for the Table Name and Singular for the the Actual Vehicle.

Hope this helps.

As a Test create a Query with all four Tables. It should show a Bunch of records. Make sure the Join is All Customers and only those Vehicles that match. Same for all other joins.

Hope this helps.

[st1300]

Go back in this thread to where I said: "The PK's and FK's look OK :-)"

You had the PK's and FK's right back then.

Remove "VehicleID" from the Services table. A particular Vehicle is not an attribute of a Service.

It looks like you have a table field for each service you can perform. that's not the way to do it. The Services table should look like this:

Table: Services
PK: ServiceID
ServiceDescription
Frequency

Then create a form for Services. Input the different services as different records in the Services table using your Services form.

Yes, the service tbl is for completed services (DATE). The whole purpose of this project is to quickly see what services have been completed and the date. So with that in mind I feel we are going in the right direction still. Heaven help me if I ever get into a complex database as I thought this was an easy one but I'm learning otherwise.

Rain: I've changed a few things you point out.

pdebaets: I've not yet done your above table mode for tblservices as I need input. Oh partial light turned maybe. With this suggested table mode the record becomes the service done and frequency could be date I guess.
I suppose what I could do then (one of many hurtles) is create a drop down box to all service preformed to be picked then date and this becomes a record attached to that vehicle for that customer.

Clear as mud

[st1300]

So, I've moved and played with the table relationships and still seem not to be getting what I think should be the results.

I've included the form and relationship capture.

With the form, the customer has 3 vehicles I wish to choose one out of the three and have the last subform show those records to that vehicle. Now yes it has been suggested I mat have not designed the last table correctly but why can't I have it show the dates like the subform "table" shows.

[st1300]

As subject may say it all.

I think the solution in fully understanding the junction table I may get this solved....

[Rainlover]

No Junction Table required. What you have done in Relationships appears correct.

Can you post a copy of the Database in 2007.

I will have a look at your forms. I think the Master and Child Relationships may be the problem.

why can't I have it show the dates like the subform "table" shows.

No reason why not. I will also look at that.

[st1300]

No Junction Table required. What you have done in Relationships appears correct.

Can you post a copy of the Database in 2007.

I will have a look at your forms. I think the Master and Child Relationships may be the problem.

why can't I have it show the dates like the subform "table" shows.

No reason why not. I will also look at that.

Thanks for reply.

I believe the subform for services completed property sheet under data and Source Object, Linked Master Fields and Linked Child Fields hold the key to our whoas? Agree somewhat Rain? I'm currently giving a go.

[Rainlover]

This may seem silly but you do realise that you can have more than one table to make your record source.

[pdebaets]

I strongly recommend changing your Services table to the layout I described above. The date completed should go on the VehicleServices table.

You can have a VehicleServices subform on your form that looks like this:

Service Date Completed
Air Filter 1/1/2011
Oil Change 2/1/2011
etc...

With the way you are headed with the Services table as it is, there will be trouble down the road.

[st1300]

This may seem silly but you do realise that you can have more than one table to make your record source.

Light bulb dim..... nothing is silly at this stage other then me trying to get past this area.

[st1300]

I strongly recommend changing your Services table to the layout I described above. The date completed should go on the VehicleServices table.

You can have a VehicleServices subform on your form that looks like this:

Service Date Completed
Air Filter 1/1/2011
Oil Change 2/1/2011
etc...

With the way you are headed with the Services table as it is, there will be trouble down the road.

Ok, I will back track one more time and see if I can understand/implement your proposed way, then we shall see what gives.