TABLE HELP

Discussion in 'Access Table Design' started by ainese, Nov 23, 2011.

  1. ainese

    ainese Guest

    Hi there,

    I have a table called Invoice:

    Service | A Usage | R Usage| Wholesale Rate| Retail A Rate | Retail R
    Rate | Total A | Total R
    Basic | 0 | 0 | 9.10 |
    11.50 | 5.75 | 0 | 0
    Track | 0 | 0 | 1410 |
    16.50 | 8.25 | 0 | 0
    B_DP | 0 | 0 | 0.00 |
    0.00 | 0.00 | 0 | 0
    BT_DP | 0 | 0 | 0.00 |
    0.00 | 0.00 | 0 | 0
    100MB | 0 | 0 | 3.00 |
    3.00 | 1.50 | 0 | 0
    500MB | 0 | 0 | 5.00 |
    5.00 | 2.50 | 0 | 0

    I want to insert individual query results into the table colums called
    'A Usage' & 'R Usage'. I've set up Total A to do a calculation and
    Total R to do a calculation once those colums are populated.

    So for example one of my query codes is below:

    SELECT Count(*) AS BasicACount
    FROM qServiceBitSumActivation
    WHERE ServiceBitSum = 0;

    The above query called BasicA returns a value of 140 and I want that
    value to go into the table under 'A Usage' where Service = Basic.

    I'm not sure how to do this as from researching through Google groups
    it's hard because I am counting in my query???

    CAN anyone PLEASE offer me some advise on how to insert/append/update
    the table pretty please???

    Thanks,
    Aine
     
    ainese, Nov 23, 2011
    #1
    1. Advertisements

  2. ainese

    Ken Snell Guest

    Query would look something like this:

    UPDATE Invoice
    SET [A Usage] = (SELECT Count(*) AS BasicACount
    FROM qServiceBitSumActivation
    WHERE ServiceBitSum = 0)
    WHERE Service = "Basic";

    --

    Ken Snell
    http://www.accessmvp.com/KDSnell/



    "ainese" <> wrote in message
    news:...
    > Hi there,
    >
    > I have a table called Invoice:
    >
    > Service | A Usage | R Usage| Wholesale Rate| Retail A Rate | Retail R
    > Rate | Total A | Total R
    > Basic | 0 | 0 | 9.10 |
    > 11.50 | 5.75 | 0 | 0
    > Track | 0 | 0 | 1410 |
    > 16.50 | 8.25 | 0 | 0
    > B_DP | 0 | 0 | 0.00 |
    > 0.00 | 0.00 | 0 | 0
    > BT_DP | 0 | 0 | 0.00 |
    > 0.00 | 0.00 | 0 | 0
    > 100MB | 0 | 0 | 3.00 |
    > 3.00 | 1.50 | 0 | 0
    > 500MB | 0 | 0 | 5.00 |
    > 5.00 | 2.50 | 0 | 0
    >
    > I want to insert individual query results into the table colums called
    > 'A Usage' & 'R Usage'. I've set up Total A to do a calculation and
    > Total R to do a calculation once those colums are populated.
    >
    > So for example one of my query codes is below:
    >
    > SELECT Count(*) AS BasicACount
    > FROM qServiceBitSumActivation
    > WHERE ServiceBitSum = 0;
    >
    > The above query called BasicA returns a value of 140 and I want that
    > value to go into the table under 'A Usage' where Service = Basic.
    >
    > I'm not sure how to do this as from researching through Google groups
    > it's hard because I am counting in my query???
    >
    > CAN anyone PLEASE offer me some advise on how to insert/append/update
    > the table pretty please???
    >
    > Thanks,
    > Aine
    >
     
    Ken Snell, Nov 23, 2011
    #2
    1. Advertisements

  3. On Tue, 22 Nov 2011 22:49:16 -0800 (PST), ainese <> wrote:

    >CAN anyone PLEASE offer me some advise on how to insert/append/update
    >the table pretty please???


    Well... generally, you don't.

    Storing derived data such as this in your table accomplishes
    three things: it wastes disk space; it wastes time (almost
    any calculation will be MUCH faster than a disk fetch); and
    most importantly, it risks data corruption. If one of the
    underlying fields is subsequently edited, you will have data
    in your table WHICH IS WRONG, and no automatic way to detect
    that fact.

    Just redo the calculation whenever you need it, either as a
    calculated field in a Query or just as you're now doing it -
    in the control source of a Form or a Report textbox.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Nov 23, 2011
    #3
    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. Dan

    Link Table to Excel Pivot table

    Dan, Aug 13, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    94
    LeAnne
    Aug 13, 2003
  2. Mary McKechnie

    Entering fadd'l field data into a table based on a lookup table

    Mary McKechnie, Sep 11, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    111
    Tim Ferguson
    Sep 12, 2003
  3. Eliphalet

    Resetting Autonumber during Make Table/Append Table

    Eliphalet, Oct 15, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    118
    Tim Ferguson
    Oct 15, 2003
  4. Bob Barnes

    List Table Field Names In Another Table

    Bob Barnes, Oct 24, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    114
    Bob Barnes
    Oct 27, 2003
  5. ABC

    Populate table with another table values

    ABC, Oct 28, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    76
    Larry
    Oct 28, 2003
  6. Mike
    Replies:
    4
    Views:
    84
  7. Peter
    Replies:
    0
    Views:
    123
    Peter
    Feb 8, 2006
  8. Ahmed Khalak
    Replies:
    1
    Views:
    95
    John W. Vinson
    Mar 27, 2008
Loading...