beginner : SQL in access

[ainese]

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

 

[sigshane]

Is it possible for you to make another table, with one column containing the service types, including all possible combinations, and a second column which contains the cost for each service type or combination?

It would seem to me to be easier if these costs are fixed, and you hard-code them into a table to avoid having to do complex calculations in the query.

Shane