By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,871 Members | 696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,871 IT Pros & Developers. It's quick & easy.

Creating Commission schedule for various companies and schedules

P: 4
Hello- I am not very familiar with access I took a class on it many many years ago, I am currently using about 15 different schedules in excel which if I make one change to 1 sheet I have to update the other 14. It was recommended to try and using access, I have watched several tutorials on relationships and primary keys and basic use, however they are not addressing my particular need, So now I am wondering if what I want to do A- can be done and B-how I can do it. I have 30 different companies which I maintain commission schedules for, I built each out in access, each company table has schedules for various contracts- example each Table for each company has 10 different payout levels , all labeled at the top by contract name but I now need to run a report by payout level for each table ( linking to one report) I don't understand how to do this using a primary key or linked data when I have 10 different payouts that I need to link to each of the tables? so I need a report for schedule 1 on one report listing all 30 carriers, one for schedule 2 and so on and so on.
3 Weeks Ago #1
Share this Question
Share on Google+
8 Replies

Expert Mod 2.5K+
P: 3,538

First, Welcome to Bytes!

To answer A: Yes it can be done (at least, I am sure it can be done), but there are many ways to get there, some better than others.

To answer B: that's the difficult part. In one sense, what you are asking for here is way beyond the scope of a typical thread. You may not get many willing to help with this, because it sounds more like "a project" to me, than a simple question that needs to be answered. In the past, I have hepped folks here with projects, and there are others who may have more or less free time to hepp with this.

However, to more directly answer you question, it sounds very much like your DB is not structured in any real way. Take a look at this link first and try to understand how table should be structured. Keep in mind that it is rather esoteric, but crucial knowledge for working with DBs.

All that said, you should have at least two tables: one that houses your companies, and one other table that houses your schedules. You say that you have ten different payouts, and I'm not sure how those are related to each company's schedule, but you may need a separate table for payouts (that is, ten records that describe what happens with each payout).

Your Schedules Table would have the fields: ScheduleID, CompanyID, PayoutID (and maybe Amount???).

Again, I don't know a lot about your data and how things are related to each other, so right now, anyone who tries to answer this thread will be guessing at this point.

However, we are willing to assist if you provide a little more information on this project. We will certainly try to aim you in the right direction.

Hope this hepps!
3 Weeks Ago #2

P: 4
Thank you for responding to my post i am uploading a sample of the information with two companies ABC and XYZ each has schedules for the payouts and each has the same contracts listed in them A1, A2, A3 ect, so I have about 30 companies like ABC and XYZ and each have about 10 schedules listed in them, what I need to do is figure out what type of relationship I can create so that I can run a query or report on each of the companies ( ABC/XYZ ect) but only pulling the schedule for A1, and a like report for each of the other relationships A-2, A3 Ect.
Attached Files
File Type: zip Test (28.1 KB, 3 views)
3 Weeks Ago #3

Expert 5K+
P: 8,745
I just had a preliminary look at the Data, and I am a little confused as to it's structure. What Fields specifically relate to the Schedules for each Company, and what Fields specifically relate to the Payouts? My initial idea is to have a 1 to MANY Relationship between Companies and Schedules and a 1 to MANY Relationship between Schedules and Payouts as poorly depicted below, but I need some more information.
Expand|Select|Wrap|Line Numbers
  1. Companies[1]==> [MANY]Schedules[1]==> [MANY]Payouts
3 Weeks Ago #4

P: 4
The fields A1, A2, A3,CMA, APM,CM,BS, BS Total all relate to payouts (BS Total is the total our company receives, the others are what we would pay out to the individual person/company), each table is a company. SO XYZ Corp has all those payouts and ABC crop has all those payouts
3 Weeks Ago #5

Expert 5K+
P: 8,745
Are the following Fields Payout Schedules, in this specific case, for ABC Corp?
Expand|Select|Wrap|Line Numbers
  1. Field1               Prem Type       Years
  2. ABC Cop        
  3. ART 2010             Target          1-1
  4. ART 2010             Renewals        2-10
  5. 10 Year Term 2010    Target          1-1
  6. 11 Year Term 2010    Renewals        2-10
  7. 12 Year Term 2010    Service Fee     10+
  8. 20 Year Term 2010    Target          1-1
  9. 21 Year Term 2010    Renewals        2-10
  10. 22 Year Term 2010    Service Fee     10+
  11. 30 Year Term 2010    Target          1-1
  12. 31 Year Term 2010    Renewals        2-10
  13. 32 Year Term 2010    Service Fee     10+
3 Weeks Ago #6

P: 4
YEs each of those products are specific to ABC Corp, I created a Table for each corp ( there are 30 corps in total ) each corp has the its own products and all the same schedules (fields A1, A2, A3,CMA, APM,CM,BS, BS Total all relate to payouts)
3 Weeks Ago #7

Expert 5K+
P: 8,745
Unless I am mistaken or misreading your intent, I feel as though this is a little more complicated then you initially had envisioned. I did, however, come up with a work-a-round that requires minimal restructuring. I'll give you an Overview then Upload the demo to see if I am even close to giving you what you had requested. From there on, we'll take it one step at a time.
  1. I created a Table named tblCompanies that contains all Company related information, such as: Company Name, Address, City, State, Zip, etc. All 30 Companies would go into this Table and be uniquely identified by a Primary Key ([CID]).
  2. I create another Table named tblSchedules that contains all Schedules and Payouts for each Company. It is involved in a 1 to MANY Relationship to tblCompanies with tblCompanies being on the '1' side. The Linking Field between them is [CID]. This Table should probably be broken down further, but this would significantly add to the complexity.
  3. I created a Unique Index in tblSchedules on the [CID], [Field1], [Prem Type], and [Years] Fields. What drove this were existing Values in these Fields.
  4. The difficult part was maintaining the Payout Structure but filtering for specific Payouts (A-1, A-2, A-3, etc.). I accomplished this by dynamically creating Queries based on selections made for Company and Payouts via 2 Combo Boxes.
  5. This is the Overview, for the complete picture Download the Demo, select a Company and Payout, then click the Command Button.
Attached Files
File Type: zip Test (32.9 KB, 4 views)
3 Weeks Ago #8

Expert 5K+
P: 8,745
Oops, just realized that I Uploaded an incomplete Demo. try this one on for size.
Attached Files
File Type: zip Test (33.5 KB, 3 views)
3 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.