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?

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.
Similar Threads
  1. BobC

    Beginner's Question ... SQL server?

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

    Beginner Question

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

    Beginner: Many to Many Relationships

    Peter, Dec 5, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    114
    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:
    179
    Reggie
    Jan 23, 2004
  5. leigh

    beginner

    leigh, Sep 2, 2004, in forum: Access Table Design
    Replies:
    4
    Views:
    97
    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:
    175
    mnature
    Feb 13, 2006
  7. JPB

    Indexes (Indices?): The Beginner's Perspective

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

    Beginner

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