access newbie pulling his hair out

[merchantguy]

I'm pulling my hair out trying to figure out how to do this.

Situation: We have customers that are brought to us by outside sales agents. These outside sales agents receive commission on the customers that they bring to us. With these outside sales agents though they have a person above them that referred the outside sales agent to us who also receives partial credit for the sales plus their is 1 other person above both of them that also receives partial credit. EXAMPLE: Bill Gates referrs Steve Ballmer to work with our office. Steve Ballmer then referrs Peter Klein to work with our office. Peter Klein then referrs Ray Ozzie to work for us. Bill Gates then receives commission for the sales done by Steve Ballmer, Peter Klein and Ray Ozzie. Steve Ballmer receives commissions only for Peter Klein. Peter Klein receives commissions only for Ray Ozzie only.



So how to build a form so that when I enter in Peter Klein as an agent and put Steve Ballmer in as a referring agent will it show that Bill Gates is tied to Steve Ballmer without my employee having to know it already.

Heck, I'm confused writing this.

[ajetrumpet]

I would guess you would be! If you want the truth, I would say do it in Excel. This complication will result in you being lost in Access, no question. Excel is so much better at complicated hiearchies like this, simply because it is flat file. There is no confusion with a flat file because there's nothing to it, really.

Would that be a viable option for you guys?

It's interesting too...this is almost exactly how Citibank has structured their commissions for their subsidiaries and financial advisors.

[merchantguy]

We currently do it in Excel but we are expanding so we have decided to move to a better environment for the handling and processing of data for all levels of our company. I currently use Access for my division and they saw what I was able to create and so they decided to move the other divisions from Excel to access. Now I'm in a pickle to figure this out. This is what I get for opening my mouth

I've always had the moto "the only things impossible are the things that nobody wants to take the time to figure out" and now I'm faced with a situation to figure out.

[ajetrumpet]

We currently do it in Excel but we are expanding so we have decided to move to a better environment for the handling and processing of data for all levels of our company. I currently use Access for my division and they saw what I was able to create and so they decided to move the other divisions from Excel to access. Now I'm in a pickle to figure this out. This is what I get for opening my mouth

I've always had the moto "the only things impossible are the things that nobody wants to take the time to figure out" and now I'm faced with a situation to figure out.

Well I wish you good luck then.

Personally, I wouldn't commit myself to a timeframe with something like this, because Access is going to be much more complex in setting this up than in other alternative programs, as I mentioned previously.

That's about all the advice I have for you. Additionally, you may want to consider offering a short term 1099 contract for the job as well, especially if the company is pressuring you to get it done, and you're still at novice level with regard to your skills.

If I were afforded the chance to guess, I would say that someone here on this forum is bound to jump in on this and start talking for free. I would as well, but I simply don't have the time to provide that much help. At any rate, good luck to you on it!