Intermediate Table linking two tables

[elfyn]

I'm currently building a DB that you will keep track of vehicles,
services to the vehicles and parts used for the servicing of the
vehicles.

I've created a table for Customers, Vehicles and Servicing which work
fine. The question I have is that I don't want to simply enter 10 x
Part Description, 10 x Part Cost in the servicing table for the user to
be able to enter the parts used .As on some occasions more parts will
be used and probably most frequently less than 10 parts will be used
for the servicing.

Looking through various DB designs I've come up with the following :-

Services Table,
Fields Service Id (PK), ServiceDate, ServiceDesc etc

Parts Used for Service Table
Fields ServiceParts (PK), Service ID (FK), ServiceParts

ServiceParts
Fields ServiceParts(PK), ServiceID, PartID, Qty etc

With this design it wouldn't then matter if the user used 1 part or
1000 parts for the service. The question is how can I set this up on an
Access form so that the intermediate table is updated correctly with
the correct values to keep the Relationship's correct between the three
tables.

I look forward to your answers in the near future.

Best Regards

Elfyn

[HS Hartkamp]

Have a form based on the services table, with a subform based on the table
ServiceParts.
In that subform, have field ServiceID hidden (at some point after testing),
have field PartID changed to a combobox, which is based on the table
PartsUsed.

In table PartsUsed, delete the column ServiceID because you don't need it
and it is confusing.

If you have more information in table Parts (e.g. part number), the you'd
make another field in the subform, similar to the PartID field, bound also
to PartID, with the combobox showing the other information through a query.
This way both fields change when you select another part.

btw, I think the names you choose are confusing.
I'd call them
- Services (Pk and perhaps with a FK to a customer table)
- Parts (PK)
- PartsUsedInService (table between the other two with FKs to Services and
Parts)

Bas Hartkamp.
<el***@ibabyshop.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I'm currently building a DB that you will keep track of vehicles,
services to the vehicles and parts used for the servicing of the
vehicles.

I've created a table for Customers, Vehicles and Servicing which work
fine. The question I have is that I don't want to simply enter 10 x
Part Description, 10 x Part Cost in the servicing table for the user to
be able to enter the parts used .As on some occasions more parts will
be used and probably most frequently less than 10 parts will be used
for the servicing.

Looking through various DB designs I've come up with the following :-

Services Table,
Fields Service Id (PK), ServiceDate, ServiceDesc etc

Parts Used for Service Table
Fields ServiceParts (PK), Service ID (FK), ServiceParts

ServiceParts
Fields ServiceParts(PK), ServiceID, PartID, Qty etc

With this design it wouldn't then matter if the user used 1 part or
1000 parts for the service. The question is how can I set this up on an
Access form so that the intermediate table is updated correctly with
the correct values to keep the Relationship's correct between the three
tables.

I look forward to your answers in the near future.

Best Regards

Elfyn