Solved

# Customer reward points calculation

Posted on 2012-09-06
242 Views
Hi experts,

I have customer reward points data. I have 2 tables.

1. CumulativeCredit -> Customer credited points and cumulative total
2. CumulativeDebit -> Customer debited points and cumulative total

From these 2 tables, I need to generate resultset to show how the customer points are debited. I mean the points debited are to be distributed as per they earned.

See attached spreadsheet for sample data and expected resultset.

I am totally mad to solve this.

Could someone have an eye on this and suggest how to achieve the result?

thanks
Points.xlsx
0
Question by:rajvja

LVL 65

Accepted Solution

Just out of curiosity, what is the purpuse of having debits and credits in two separate sets?
Why not the same set, with one as negative points, the other as positive points.

Also, looking at your spreadsheet each row in both have a unique TransID, so I don't see what the relationship is between debit row and credit row.  So if there is none, why try to display one?
0

LVL 28

Assisted Solution

Are you looking for something like a monthly breakdown of how points are earned and spent? If so, you could get a list of months using a process like this:

http://trycatchfinally.net/2012/03/return-a-list-of-all-dates-between-a-start-and-end-date/

And then sum your debits and credits to show how they've been used. If that's not what you're looking for, then I might need some more detail - your requirements seem a bit vague.
0

LVL 11

Author Comment

Hi ryanmccauley,

Thanks for the reply. I dont want monthly breakdown.

Yes the requirement is nasty. I want to split the debit points based on credit date.
For example, if I have used 500 points today that means definitely i should have earned those points earlier. But they might have earned on different dates.

My requirement is to split 500 pts when they have earned.

I hope it is clear.
0

LVL 11

Author Comment

Thanks for the replies. It hasn't resolved my issue.
0

LVL 11

Author Comment

I've requested that this question be closed as follows:

Accepted answer: 0 points for rajvja's comment #a38382526
Assisted answer: 250 points for jimhorn's comment #a38375934
Assisted answer: 250 points for ryanmccauley's comment #a38377444

for the following reason:

Not resolved my issue
0

## Featured Post

### Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…