Complex query in ms access Hi, at this point I’m not sure how to solve this issue either through a querry or VBA code.
I’m building a sales report where I have data from 2 tables
1. Customer Revenue Trend (when we bill a customer it goes in that table)
2. Sales_Performance table (this table is like a forecast table where our sales force declare the business they have won, the amount, a start date (which is when we should see revenue in the door as in billed), by product.
Our sales rep have 12 months to realize the revenue from this table which will show in the [Customer Revenue Trend] tbl
The Customer Revenue Trend table contains revenue by customer by product, by month (P1 as January)
Fields:
Parent
CustName
CustNumber
ProductCode
2012-P1
2012 P2
2012 P3 .....
2013 P1
2013 P2
2013 P3
Sales_Performance
Parent
CustName
CustNumber
ProductCode
Sart date Year
Sart date month (p1, p2, p3…p12)
My current process/report identifies new revenue versus existing revenue by determining if the customer had revenue prior year or not. If revenue existed for a customer at the parent level by product it’s flagged as existing revenue otherwise new revenue…all is done from the [Customer Revenue Trend] tbl
The problem is sometimes I flag revenue as existing and it should be “New” on the following basis. The business was won in November 2012, from [Sales_Performance] TBL , and in the billed revenue table [Customer Revenue Trend] tbl we start seing revenue in Novermber 2013 Now in 2013 when I run my report any customer with revenue in 2012 is considered as new .
When I was running the report in 2012 it would have been new revenue assuming there was no revenue in 2011. Because they have 12 months to realise the revenue the new revenue status should carry as “NEW” until October 2013 .
I’m being ask to change the old method to show the revenue as new base on the fact that it was won in November 2012 and the billing started in Nov 2012 this customers revenue should be New in 2013 for another 10 month to run the entire 12 month cycle as new revenue.
I hope this is clear enough to get some help
I hope this is clear enough to get some help
[Customer Revenue Trend] tbl
|
Parent
|
CustName
|
CustNumber
|
ProductCode
|
2013 P1
|
2013 P2
|
2013 P3
|
2013 P4
|
2013 P5
|
2013 P6
|
2013 P7
|
2013 P8
|
2013 P9
|
|
ABC Inc
|
abc
|
3000996
|
5018
|
1000
|
0
|
0
|
2000
|
0
|
0
|
0
|
0
|
0
|
|
ABC Inc
|
abc
|
3000996
|
5021
|
0
|
0
|
469
|
0
|
2,600
|
0
|
0
|
0
|
|
[Sales_Performance] TBL
|
Parent
|
CustName
|
CustNumber
|
ProductCode
|
Revenue
|
YEAR WON
|
PERIOD WON
|
|
ABC Inc
|
abc
|
3000996
|
5021
|
20000.9
|
2012
|
11
|
|
ABC Inc
|
abc
|
3000996
|
5022
|
35000
|
2012
|
1
|
Example of the issue
The revenue should be new base on the rules explained above because it was new in late 2012 and should remain as new for 12 month. I have multiple customers in that situation but all for product 5021
Look of final report
|
Sort
|
Parent
|
CustNumber
|
CustName
|
Category
|
product
|
YEAR
|
YTD
|
P1
|
P2
|
P3
|
P4
|
P5
|
P6
|
P7
|
P8
|
P9
|
P10
|
P11
|
P12
|
FY
|
|
|
ABC Inc
|
0001699
|
abc
|
EXISTING
|
5021
|
2012
|
2,437
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
2,437
|
2,437
|
|
|
|
|
|
|
|
2013
|
3,069
|
0
|
0
|
469
|
0
|
2,600
|
0
|
0
|
0
|
0
|
0
|
0
|
0
|
3,069
|
|
|
|
|
|
|
|
2013YoY2012
|
632
|
0
|
0
|
469
|
0
|
2,600
|
0
|
-2,437
|
0
|
0
|
0
|
0
|
0
|
632
|