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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. BobC

    Beginner's Question ... SQL server?

    BobC, Nov 5, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    141
    Tim Ferguson
    Nov 5, 2003
  2. JFG

    Beginner Question

    JFG, Nov 10, 2003, in forum: Access Table Design
    Replies:
    0
    Views:
    75
  3. Peter

    Beginner: Many to Many Relationships

    Peter, Dec 5, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    93
    Tim Ferguson
    Dec 5, 2003
  4. Walter Donavan

    good free access 2002 beginner tutorials?

    Walter Donavan, Jan 21, 2004, in forum: Access Table Design
    Replies:
    9
    Views:
    136
    Reggie
    Jan 23, 2004
  5. leigh

    beginner

    leigh, Sep 2, 2004, in forum: Access Table Design
    Replies:
    4
    Views:
    82
    Allen Browne
    Sep 2, 2004
  6. Kegan Longridge

    beginner w/Relationship prblm

    Kegan Longridge, Feb 10, 2006, in forum: Access Table Design
    Replies:
    3
    Views:
    158
    mnature
    Feb 13, 2006
  7. JPB

    Indexes (Indices?): The Beginner's Perspective

    JPB, May 25, 2006, in forum: Access Table Design
    Replies:
    5
    Views:
    54
    Jerry Whittle
    May 26, 2006
  8. KGUY

    Beginner

    KGUY, May 5, 2009, in forum: Access Table Design
    Replies:
    8
    Views:
    151
    Jeff Boyce
    May 6, 2009
Loading...