Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    8

    Unanswered: Storing Query Sum in a Field

    Anyone know how to store a Query sum result in a field?

    Shouldn't it be a default value or something?


    My Query is working fine, just ned to store the results in a table row - thanks!

  2. #2
    Join Date
    Mar 2005
    Posts
    8
    =Sum()

    I can make this a default value of a number field, but where can I reference my query?

    =Sum([SQL: statements go here])

    or preferably:

    =Sum([specific saved query in query box])

  3. #3
    Join Date
    Mar 2005
    Posts
    8
    Sorry, switching databases isn't pretty.

    I really meant that I wanted to store a count, not a sum.

    My SQL statement is a count. A CountOfID to be specific.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there are ways to do such things using either the insert or update sql statements.

    why would you want or need to store a derived value within your database? it goes agianst all the principles behind a relational database.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2005
    Posts
    8
    Ah, its because the values need to be discreet and time stamped. I need to know what those values were at a specific time.

    Thanks for the reply. I guess I could just use the insert query. Can you store multiple statements in a single query?

  6. #6
    Join Date
    Mar 2005
    Posts
    8
    sounds like i need to make a module and run the SQL queries from there...

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So store the date time of the sale as part of the sale record. ITeh basic point over the tabel design is to store information where it is relevant.

    Doing it the way you are doing, how do you know that you have the correct sales records in your total. Waht happens if a sales record is missattributed to the previous period, waht happens if you find a bundle of sales records that didn't get field when they should.

    if its derived data, then unless you have a very good reason extract the data using a query at runtime, not at data collection time. The only times I have seen a need for storing such derived data was with a pig of a financial securities system, where the company needed to builid in a series of checkpoints to track and monitor fraud.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2005
    Posts
    8

    Hmmm...

    I do. It's a government form used during oil spills. It's called a 209. So, my table keeps track of each copy of the form with as many fields filled out by the system as possible. Each copy of the form needs to be time stamped and archived in a 209s table.

    The data is coming from a resources table that constantly changes during the day/hour/min. Resources are not added or subtracted often. If all I was doing was inserting new records all the time then I could see your point. I am however updating records constantly. So I have this 209 table that totals up things like type=skimmers AND status=assigned, and provides a memo field or two for notes and things that the system can't fill out for each 209.
    Last edited by BlackHaloBender; 03-01-06 at 13:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •