473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Creating Commission schedule for various companies and schedules

4 Nibble
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.
Sep 22 '20 #1
8 2182
twinnyfo
3,653 Expert Mod 2GB
Lwaugh,

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!
Sep 22 '20 #2
Lwaugh
4 Nibble
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 Data.zip (28.1 KB, 13 views)
Sep 24 '20 #3
ADezii
8,834 Expert 8TB
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
Sep 25 '20 #4
Lwaugh
4 Nibble
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
Sep 25 '20 #5
ADezii
8,834 Expert 8TB
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+
  14.  
Sep 25 '20 #6
Lwaugh
4 Nibble
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)
Sep 25 '20 #7
ADezii
8,834 Expert 8TB
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 Data.zip (32.9 KB, 31 views)
Sep 25 '20 #8
ADezii
8,834 Expert 8TB
Oops, just realized that I Uploaded an incomplete Demo. try this one on for size.
Attached Files
File Type: zip Test Data.zip (33.5 KB, 16 views)
Sep 26 '20 #9

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

Similar topics

5
by: angelasg | last post by:
I am working with employee schedules. Each schedule is comprised of segments (shift, lunch, break, training, etc.) that have rankings. Each record has the employee id, the date the shift starts,...
0
by: groups | last post by:
Hello, I am building a diet/weight loss website where a customer would subscribe for 1,3,6 or 12 months. I am using Paypal IPN to handle the subscription payments. I would like to create a...
1
by: bigfella | last post by:
Hi Everyone, I am using the following code (many thanks for the code folks BTW) to generate 3 schedules in three tables one is a schedule with 7 day intervals and two are daily schedules. The...
2
by: Smriti Dev | last post by:
Hi, I am creating a form that allows users' to enter dates and times staff are available for work. They are 2-3 different types of staff. For example, permanent staff that work M-F 9-5 pm...
4
by: Michel Verhagen | last post by:
Hi, I want to create a DTD in an XML file in memory. The XML file is created using the DOM in C#. I am new to all this, but couldn't find anything about creating DTD's using the DOM (well, I...
7
by: hgirma | last post by:
Hello Gurus, Is it possible to schedule a task to run an application deployed using ClickOnce? The executable changes location with each update.. and if i were to run the executable directly,...
62
by: Wiretwisterz | last post by:
I am using Microsoft Office XP and I have developed an Access database that is used to schedule conference and training rooms within buildings. I have one main form named "Events" that is used to...
2
by: spima05 | last post by:
Hello I am trying to create a database to calculate commissions on a sale based on a tiered commission schedule and am having trouble with how to design the tables and relationships to store the...
12
by: spima05 | last post by:
Hello I am developing a database to calculate commissions on a sale for each rep involved in the same and their uplines. Below is the database structure and the commissions schedule. I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.