Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Store calculated field in table (Access 2002)

    I would like to store a calculated value in a table. How is this possible?

    tblDiscretionaryDetails stores the following information:
    CountyID, EventID, DiscretionaryAmt

    I want tblCounties.discretionaryexpenditures to be the stored calculated sum of all the CountyID's and DiscretionaryAmt's from tblDiscretionaryDetails.

    tblDiscrtionaryDetails could contain a couple countyID's for each eventID. Which means that over several eventid's, there will be repeats of countyid's so I'm looking to group on that county ID and keep a sum stored in the table as it will be used for other calculations. I know how to make a qry that works but how do you store back to the table And will it be continuously updated?

    any comments on this is appreciated.

    Thanks. Jenn

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Store calculated field in table (Access 2002)

    Hi Jenn


    By definition you cannot have calculated fields in a table, but you can create calculations in a query. It is very difficult to follow your written example as you cannot sum a primary key as the answer would be nonsense , however you can count the primary key (I am assuming CountyID is the primary key of the table).

    Can you add a cut down version of the database to your reply to this or at least a screen dump of the relationships to assist how you have created the relationships.

    If you do not want to do this, reading your last comment in the last paragraph, you can create a make table query to do updates but I think this method is a sledgehammer to crack a nut so I feel we need to review the database schema
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store calculated field in table (Access 2002)

    I apologize... County ID is not the primary key... I didn't include the primary key in the post as it's an autonumber and irrelevant to the point. I was looking primarily for a counter reason to my approach, which you provided! I thought I would try and store the calculated field in a table rather than setting up a qry and rpt to pull the data when I need it. But I suppose I will have to. I built a qry which sums the discretionaryamt field by grouping on countyID and yields an accurate result.

    Anyway, You can view the relationships in the attachment... This is the first stage of a complete overhaul and redesign of a database that was used for years... We are adding new features and capabilities and ironing out some limitations that were built into previous versions. Any suggestions are welcome.

    Thanks much for your response.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store calculated field in table (Access 2002)

    When I open the snapshot file in your attachment, it is blank except for the text in the upper left corner [Relationships for PAC07-08(2)]

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store calculated field in table (Access 2002)

    Best I could do for right now is a printscreen of the relationships...

    Hold onto this Hans... I'm sure I'll be posting other questions...

    Thanks...
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store calculated field in table (Access 2002)

    I have to apologize, I somehow missed the second and third page in your snapshot file. Now that I saw them in the PDF file, I went back and discovered them in the snapshot too. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store calculated field in table (Access 2002)

    But now that I see the relationships, I fail to see tblDiscretionaryDetails. Must be my old eyes...

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Store calculated field in table (Access 2002)

    Be glad It's not your eyes Hans!

    Here's the accurate relationship view.

    Again, I'll take any suggestions...

    Jenn
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Store calculated field in table (Access 2002)

    You can create a query based on tblDiscretionaryDetail.
    Add the CntyID and Amount fields to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Set the Total option for Amount to Sum (the one for CntyID remains Group By, the default setting).
    This query will return the total amount for each county. Save it under an appropriate name, e.g. qryAmountPerCounty.

    You can use it whenever you need this amount. For example, if you need it in another query, add qryAmountPerCounty and join it to another table on CntyID.
    Using a query guarantees that the total amount will always be up-to-date; if you stored it in the tblCounties table, you'd run the risk that the amount would lag behind the actual situation.

Posting Permissions

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