mikeytt Posted January 12, 2011 Posted January 12, 2011 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 Posted January 12, 2011 Posted January 12, 2011 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 Posted January 12, 2011 Author Posted January 12, 2011 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 Posted January 12, 2011 Posted January 12, 2011 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 Posted January 12, 2011 Author Posted January 12, 2011 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
Recommended Posts
This topic is 5124 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 accountSign in
Already have an account? Sign in here.
Sign In Now