beginner : SQL in access

Discussion in 'Access Table Design' started by ainese, Oct 30, 2011.

  1. ainese

    ainese Guest

    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,
    ainese, Oct 30, 2011
    1. Advertisements

  2. ainese


    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.

    , Apr 9, 2013
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.