Hi there,
I have 4 colums of data (with 2,000 rows)
Column 1: Subscriber Types (2)
Column 2: Serial Numbers (2000)
Column 3: Service Types (5)
Column 4: Rates
Column 1: There are 2 subscriber types: Type_A, Type_R
Column 3: There are 5 Service Types: B, T, DP, H, F
Column 2: The Serial numbers in Column 2 can have more than 1 Service
Type
associated with it.
Column 4: The rate associated with the service Type
B, T, H, F have set rates.
DP is a variable rate. (it depends on if the service type includes B +
DP or
B & DP + T)
For example:
Serial number 123456 can have service types B + T + H if this is the
case I
apply a set rate
My issue arises when when a serial number has service types: B + DP or
B + T+ DP. A special rate applies to B + DP and a different rate
applies to
B + T + DP.
So what I have done is in access using sql (unsuccessfully) is
counted service type
apply the rate
Produce invoice
What I want to do is:
select by distinct serial number
check the types of services associated with it
add all the B's, T's, H's and F's up
IF service type = DP then check if B or B & T are associated
create 2 new service types B_DP & T_DP so
when service type = B,T,DP service T_DP is assigned to it and
when service type = B,DP service B_DP is assigned to it.
Then I want create a basic table showing a breakdown of the
calculations.
Service Type Usage number Rates
Count B xx $$
Count T xx $$
Count H xx $$
Count F xx $$
Count B_DP xx $$
Count T_DP xx $$
I can do it for all service types except when it comes to when service
type
DP. I don't know how to code it..
Can anyone offer advise on this please
Thanking you kindly in advance,
Aine
|
