Jump to content

How to add a third relationship


This topic is 4407 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!


Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 4407 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.