Use DAO to write summary of one table into another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

inews@questuscorp.com - 19 May 2008 08:03 GMT

I want to sum all values in the profit field of table T and write it
to Table Sys.

I have instantiated object d as a DAO database object and various
other UPDATE queries are running fine, but only when I calculate from
values in the same table. I just can't seem to find the right syntax.

d.Execute "UPDATE Sys SET Sys.NetProfit = SUM[T]![Profit];"

Beyond this, I would like to restrict the sum of profits to only those
whose row has a particular value in another field in T.

Any thoughts? Any alternative paths?

Reply to this Message

Roger - 20 May 2008 11:50 GMT

On May 19, 1:03 am, in...@questuscorp.com wrote:

> I want to sum all values in the profit field of table T and write it
> to Table Sys.
[quoted text clipped - 9 lines]
>
> Any thoughts? Any alternative paths?

use 2 queries
first set netProfit to 0, use the 'where' to select which rows
UPDATE Sys SET Sys.netProfit = 0
WHERE (((Sys.id)=1));

then sum profit with
UPDATE Sys INNER JOIN T ON Sys.id = T.id SET Sys.netProfit = [sys].
[netProfit]+[t].[profit]
WHERE (((Sys.id)=1));

Reply to this Message

lyle fairfield - 20 May 2008 12:19 GMT

On May 19, 3:03 am, in...@questuscorp.com wrote:

> Any thoughts?

Yes. Don't store calculated fields.

Reply to this Message

Builder - 21 May 2008 14:31 GMT

> On May 19, 3:03 am, in...@questuscorp.com wrote:
>
>> Any thoughts?
>
> Yes. Don't store calculated fields.

And just pretend there's no need for data warehouses and aggregate fact
tables...

Reply to this Message

lyle fairfield - 21 May 2008 15:05 GMT

"Builder" <nospam@buildit.com> wrote in news:QtVYj.16949$255.8241
@bignews8.bellsouth.net:

>> On May 19, 3:03 am, in...@questuscorp.com wrote:
>>
[quoted text clipped - 4 lines]
> And just pretend there's no need for data warehouses and aggregate fact
> tables...

One doesn't have to pretend something that's true.

Reply to this Message

The Frog - 22 May 2008 13:11 GMT

Stop using ROLAP and start using something a little more advanced
maybe? MOLAP perhaps?

Reply to this Message

Rate this thread: