How to keep track of referrals, 3 referrals deep?

[JTSmith]

I'm trying to build a DB that I can enter in a customer, and how much he spent per month, then track who referred him. The problem is Customer A earns 1 point for every dollar spent in a month, he also earns 1 point for every dollar spent by someone he refers (Customer . But Customer B refers Customer C. Customer A receives 1/2 a point for every dolar customer C spends, and 1/4 point for every dollar customer D spends, who is referred by Customer C.

I sincerely apologize for the terrible and confusing example, but I need a way to enter in every customer's amount spent, then caclulate the number of points each person earns in that period...

Any help would be appreciated. Thanks guys.

[Vaughan]

Interesting issue.

How far back does the referral go?

I can see a self-join and a calculation that sums the related records * 0,5 which will automatically half the amount for each referal back.

The devil is in the detail though.

[comment]

The devil is indeed in the details - and while I can see "3 referrals deep" in the title, it's the spending part that's not quite clear to me. Will there be a single amount spent by a person in each month - or can a customer spend several amounts in the same month?

[JTSmith]

The devil is indeed in the details - and while I can see "3 referrals deep" in the title, it's the spending part that's not quite clear to me. Will there be a single amount spent by a person in each month - or can a customer spend several amounts in the same month?

Yes, but basically what I will do is use Quickbooks and pull how much total was spent in each month by each customer, then i will place all the amounts spent by each customer, and then calculate how much each person earns. I don't need it so much for accounting, as a glorified calculator to tell me how much to pay people. A history would be nice as well, to see how much people earned total over the year, etc, but the primary goal is to track the referrals and be able to calculate how much they earn each month.

Interesting issue.

How far back does the referral go?

I can see a self-join and a calculation that sums the related records * 0,5 which will automatically half the amount for each referal back.

The devil is in the detail though.

I have only had the program a few months, and I have done them all manually, but as it's growing, it's clear I need a computerized system. I would start it from now on, I don't need to import any data. Thanks!

[comment]

then i will place all the amounts spent by each customer

Well, that's the crucial point here: where will you place those amounts. If it's in the Customers table, then you can have only one amount per customer at a time. Which may be fine, if - as you say - you just want to calculate the points ad hoc, without keeping history. In fact, it's very simple to do it this way.

[JTSmith]

Well, that's the crucial point here: where will you place those amounts. If it's in the Customers table, then you can have only one amount per customer at a time. Which may be fine, if - as you say - you just want to calculate the points ad hoc, without keeping history. In fact, it's very simple to do it this way.

Any chance you could point me in the right direction, or let me know the way to do it? Thanks.

[comment]

I was waiting for you to confirm one amount per customer at a time is satisfactory.

[JTSmith]

I was waiting for you to confirm one amount per customer at a time is satisfactory.

Oh, ok, sorry. Yes, that's all I need. History would be great, and I can work on that later, but if I can at least get the calculation part up and running, that will help me to do the reports now. Thanks.

[comment]

See if this helps:

Pyramide.zip

[JTSmith]

See if this helps:

Hey, I GREATLY appreciate your assistance. I haven't been back to work for a while, but I just downloaded it and it appears to do everything I need! Thank you again... Maybe you can send me your email so I have it on file in case I do a big job and need a consultant? Thanks.

[comment]

I've sent you a private message.