Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2020
    Posts
    2

    Capture the value of a calculated field.

    Hello!

    Is there a way to capture the value of a calculated field and pass that value to a table?

    Specifically, I have a job cost report that has a subreport for labor. The labor is grouped by labor class (General, painting, electrical...) Each class gets summed and displayed on the report. I'd like be able to use those sums in another report that compares actual labor to estimates.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Unless you have a specific reason for doing so, you should not store calculated values. It is rare that you should.
    Either repeat the calculated field or summing in footer in other reports or make the calculation part of the query, especially if you can use that query for other reports.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Mar 2020
    Posts
    2
    Thanks for your reply. I do have a specific reason for storing the calculated values. I'd like to compare the actual labor by category to the estimates and calculated the difference.

  4. #4
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Is there a way to capture the value of a calculated field and pass that value to a table?
    your question doesn't have an awful lot in common with what you are trying to do. the terminology is getting in the way of comprehension. and, specifically, you do not have a calculated field, and there is no need to pass that value to a table.

    what you have is query with a calculated value, which is displayed in a control on a report. that is an entirely different beast altogether.

    Specifically, I have a job cost report that has a subreport for labor. The labor is grouped by labor class (General, painting, electrical...) Each class gets summed and displayed on the report. I'd like be able to use those sums in another report that compares actual labor to estimates.
    each class total already exists in a query, that is the recordsource for the report.

    open the report in Design View

    press F4 to view the Property Sheet for the Report.

    click the Data tab and see what it says under Record Source. it will almost certainly be the name of a query or a SELECT statement. to refer to any of the values in that query just give the query name as your recordsource. there is no need to "store" the value in a table because it already exists in your database.


    many thanks,


    Cottonshirt

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    what you have is query with a calculated value, which is displayed in a  control on a report. that is an entirely different beast altogether.
    Where is that coming from? The calculations are done over a group summary or footer in the report. That is an feature that makes reports so useful for this sort of thing and doesn't require the calculation to be in a query.
    Click image for larger version. 

Name:	rptTotals.jpg 
Views:	13 
Size:	24.2 KB 
ID:	41563

    I'd like to compare the actual labor by category to the estimates and calculated the difference.
    That is not a valid reason. Actual costs should be calculated on the fly. Estimated costs calculated on the fly. Calculated difference becomes the result of 2 calculations. However, you are free to do whatever suits you, of course.

    Some of this is out of date, but worth your time I think
    http://allenbrowne.com/casu-14.html
    http://allenbrowne.com/ser-45.html

  6. #6
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Micron said: Where is that coming from?
    assuming that is a legitimate question...

    OP said: Each class gets summed and displayed on the report.

    I interpret that as meaning that the sum is done first, then displayed on the report. which implies an aggregate query.

    I agree that the sum can be done in the report, but just because it can be done that way does not mean the OP is actually doing it that way. you may be right. I don't know, but that's how I interpret what OP said.

    either way, whether the sum is done in a query or in the report, there is no need to save the answer in a table in order to use it somehere else, which was his underlying question. I'm sure we can agree on that.



    many thanks,


    Cottonshirt

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    OP has report, which has some query or table as source. In report, some report-level grouping is made, and some values are calculated group-wise. OP wants to use those calculated values in another report.

    In case OP uses unsaved query in original report as source, he/she creates a saved query which don't use parameters. This query may be more general one - i.e. OP may get same report when he/she uses report source like
    Code:
    SELECT * FROM SavedQuery <WHERE SomeConditions are TRUE>
    OP creates another saved query/several saved queries (aggregate) based on first one, with same filters, groupings and calculated values as in original report source. This/Those query/queries can be used in source query/queries of another report(s), e.g. using Join.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-11-2018, 07:19 AM
  2. Replies: 1
    Last Post: 12-10-2017, 01:47 AM
  3. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  4. Capture Value From Text Field
    By jo15765 in forum Forms
    Replies: 3
    Last Post: 03-15-2017, 06:54 PM
  5. Replies: 2
    Last Post: 12-30-2014, 01:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums