beginner : SQL in access



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
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
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
DP. I don't know how to code it..

Can anyone offer advise on this please

Thanking you kindly in advance,


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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question