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

  2. ainese

    Guest

    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
     
    , Apr 9, 2013
    #2
    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.