Use DAO to write summary of one table into another

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • inews@questuscorp.com
    #1

    Use DAO to write summary of one table into another

    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?
    Tags: None
    • Roger
      #2
      Re: Use DAO to write summary of one table into another

      On May 19, 1:03 am, in...@questusco rp.com wrote:
      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?
      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));

        Comment

        • lyle fairfield
          #3
          Re: Use DAO to write summary of one table into another

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

            Comment

            • Builder
              #4
              Re: Use DAO to write summary of one table into another

              lyle fairfield wrote:
              On May 19, 3:03 am, in...@questusco rp.com wrote:
              >
              >Any thoughts?
              >
              Yes. Don't store calculated fields.
              And just pretend there's no need for data warehouses and aggregate fact
              tables...


                Comment

                • lyle fairfield
                  #5
                  Re: Use DAO to write summary of one table into another

                  "Builder" <nospam@buildit .comwrote in news:QtVYj.1694 9$255.8241
                  @bignews8.bells outh.net:
                  lyle fairfield wrote:
                  >On May 19, 3:03 am, in...@questusco rp.com wrote:
                  >>
                  >>Any thoughts?
                  >>
                  >Yes. Don't store calculated fields.
                  >
                  And just pretend there's no need for data warehouses and aggregate fact
                  tables...
                  One doesn't have to pretend something that's true.

                    Comment

                    • The Frog
                      #6
                      Re: Use DAO to write summary of one table into another

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

                        Comment

                        Previous template Next
                        Working...