Use DAO to write summary of one table into another Options

  • Thread starter Thread starter inews
  • Start date Start date
I

inews

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?
 
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?


So this "Sys" table has only a single record, a summary record of some sort?
Do you really need that? After all, you can calculate the sum any time you
want it with a simple totals query. And that way, you'd always know it was
up to date and accurate.

But if you do need to actually calculate and store this sum, you can do it
with a query that invokes the DSum function -- provided the query is run
from Access, not from some other application that just uses DAO. Here's an
(untested) example:

d.Execute _
"UPDATE Sys SET Sys.NetProfit = " & _
"DSum('Profit', 'T', 'SomeField=SomeValue')", _
dbFailOnError
 
Thank you very much Dirk.

The Sys table will have a number of records. I was trying to get the
simplest thing working first.

The next step is to sum only a subset of records from T for each row
in Sys. So, as with your example 'SomeField' exists in Sys, whose
values will be used to restrict values using 'T.SomeField'.

I agree about calculating dynamically. Unfortunately the data
structure is dictated from above. Also, the data will not change once
stored.

Your example looks like the goods. Will test and report back.

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?

So this "Sys" table has only a single record, a summary record of some sort?
Do you really need that?  After all, you can calculate the sum any time you
want it with a simple totals query.  And that way, you'd always know it was
up to date and accurate.

But if you do need to actually calculate and store this sum, you can do it
with a query that invokes the DSum function -- provided the query is run
from Access, not from some other application that just uses DAO.  Here'san
(untested) example:

    d.Execute _
        "UPDATE Sys SET Sys.NetProfit = " & _
            "DSum('Profit', 'T', 'SomeField=SomeValue')", _
        dbFailOnError

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
 
Thanks again Dirk.

This is very close to what I need.

Unfortunately DSum calculates on the values in the MDB file holding
the code (all zero) instead of the values in database d. Tested by
changing values from zero to 111 in that file and the code summed them
- instead of the values in database d.

Source and target fields are all in the DAO object.

How do I force DSum to calculate using values from fields in database
d (the DAO object)? That is, the domain for DSum should somehow point
at the DAO object.

'd.T' throws an "Unknown" error.

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?

So this "Sys" table has only a single record, a summary record of some sort?
Do you really need that?  After all, you can calculate the sum any time you
want it with a simple totals query.  And that way, you'd always know it was
up to date and accurate.

But if you do need to actually calculate and store this sum, you can do it
with a query that invokes the DSum function -- provided the query is run
from Access, not from some other application that just uses DAO.  Here'san
(untested) example:

    d.Execute _
        "UPDATE Sys SET Sys.NetProfit = " & _
            "DSum('Profit', 'T', 'SomeField=SomeValue')", _
        dbFailOnError

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
 
Thanks again Dirk.

This is very close to what I need.

Unfortunately DSum calculates on the values in the MDB file holding the
code (all zero) instead of the values in database d. Tested by changing
values from zero to 111 in that file and the code summed them - instead of
the values in database d.

Source and target fields are all in the DAO object.

How do I force DSum to calculate using values from fields in database d
(the DAO object)? That is, the domain for DSum should somehow point at
the DAO object.

'd.T' throws an "Unknown" error.

I didn't understand from your previous message that the code was running in
a different database from the one holding the data. You're right, DSum is
not going to know about that.

It's more cumbersome, but you can get the sum in advance by opening a
recordset on a totals query, and then stick the result as a literal into
your update query. Like this:

Dim rs As DAO.Recordset

With d

Set rs = d.OpenRecordset( _
"SELECT Sum(Profit) FROM T WHERE SomeField=SomeValue")

.Execute "UPDATE Sys SET NetProfit = " & rs(0), dbFailOnError

rs.Close

End With

You haven't given me enough information to adjust the above example as might
be required by the circumstance you referred to in this remark:
 
Back
Top