How to add a third relationship

[mikeytt]

Very stuck on this one, appreciate any help.

Example File attached.

I have a table called SERVICES which contains the names of various services.

In turn there is a related table called COSTS, which has a breakdown of costs for each service in the SERVICES table

So for example:

Services table has the following records (the number of records is not limited, though they will have different names):

Catering, Cleaning, Training

Costs table has (each an individual record):

Sandwiches £5, Drinks £2, Hot Food £10 – related to CATERING in the services table

Staff £10, Equipment £30, Fluid £3 – related to CLEANING in the services table

Travel £30, Courses £100 – related to TRAINING in the services table

In the SERVICES table, the sum of costs for each service is shown, so for example Catering would = £17 (5+2+10)

OK, this all works.

Now, my problem comes where I have to add commissions to the services. The number of commissions can be unlimited and can apply to all, some or none of the services.

For example, with three commissions:

Referral = 10%, applies to all three services

Venue = 15%, applies to catering and cleaning only

Booking = 10% applies only to training

I want to be able to show the commission costs in the the SERVICES table

For example:

catering: Costs = £17, Commission = 25% (10 % + 15%), Total cost = £21.25 ( £17 * 1.25 )

So this is where I am completely stuck. i just cannot work out what sort of relationship to set up to get this to work. If anyone can point me in the right direction that would be fantastic!

Example.fp7.zip

[comment]

I don't understand your example: in the Services table, will there only be one record for "Catering"? Or is "Catering" a category that could include many Services records?

[mikeytt]

I don't understand your example: in the Services table, will there only be one record for "Catering"? Or is "Catering" a category that could include many Services records?

Catering is a single record which will have many COSTS.

There will only be one record for catering, one record for cleaning, one record for training in the Services table. The list of services is not limited though, there could be 5, 10 or 100 more (but each would have different names).

I've edited in the main message how i've explained the costs table as well

[comment]

Have a look at the attached for one way to look at it.

However, I must add that I am a bit uneasy about this: I can't quite see what the purpose of this file is. It looks more like some sort of calculator than a database.

Example1.zip

[mikeytt]

Fantastic, thank you - that's exactly what I was after.

It's just one small part of a much larger database i'm doing for my brother's new Event company.

This bit will be forming part of a quotation system.

Thank's once again