ainese
unread,Oct 29, 2011, 8:39:42 PM10/29/11You do not have permission to delete messages in this group
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to
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