TABLE HELP

[ainese]

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]

Query would look something like this:

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

 

[John W. Vinson]

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