Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Unanswered: Summing a calculation in one footer to the next level footer

    I have a report in Access 2007 that list volunteers according to three levels of their position (Department, Area, Position). At the end of the detail in my first footer (Position) I count the volunteers [ActCap] and then take a factor to indicate the Realistic Capacity. But if the count is 2 or less, I want to assume 1. My formula is (iif ([ActCap]<=2, 1, ([ActCap]*.333)). I've named this result [RealCap]. Both of these work great.

    I then want to create the next footer for (Area). I can simply count the volunteers to get the total volunteers at this level, but when I try to sum the [RealCap] an "Enter Parameter Value" box pops up. I've tried putting a new box in the [Position] footer called [RealCapValue] which is just =[RealCap] and then in the [Area] footer do =Sum[RealCapValue] but again I get an "Enter Parameter Value" box pops up. I can't do the same calculation as in the Position footer because some of the calculations assume 1 rather than the formula.

    I'm stumped. Any and all help would be appreciated!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    several ways
    ..you can do this in code running in background. place the seroing, summation and assignment to the relevant events
    OR
    ..you can do this by carrying forward the values as hidden controls and referring to them elsewhere
    a technique is int he detail row use a hidden control for each category then do your final calcuation elsewhere
    ...or use domain functions DCOUNT, DUM etc in the relevant group footer(s), Domain function can have their uses in reports and forms, but if you find you are using 'em a lot then its probably better to restructure the report to use a recordset
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2016
    Posts
    2

    Pulling additional data into the footer.

    I'm relatively new to Access so your answer was mostly over my head but I think I got a resolution to my first problem with that information. Now that I have that problem fixed I have an additional problem. My detail is a list of people sorted by Position, Area and then Department. They each have a Job Code assigned to them that shows Department/Area/Position which I use to sort them. In my first footer I do a count of these people by their "position" within that Area and Department. In the same footer I want to bring in a number that says what a minimum should be for this position to then compare it to this actual count. I tried doing a DLOOKUP from another table that has a list of Job Codes and the minimum but that does not seems to work. I can't figure out how to put this single number in the detail section so that I can then pull it down to the footer since it does not have a name attached to it which is what I am pulling into the detail. And I anticipate that once I get this first footer to work, I will then have a similar problem for the Area footer and then the Department footer.

Posting Permissions

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