Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make a Table w/Totals (Access 2k)

    Good Morning,

    I'm looking for a suggestion here....

    I have a table (tblPrevMthTotals) that ONLY holds 1 record with fields such as OfferedCalls, AbandonedCalls etc. (15 fields.)

    The purpose of this table is to hold LAST mths TOTAL figures . That's it's only purpose.

    Currently, the user opens a report that has a subReport attached, the subReport is nothing more than SUMS and AVG for the fields in tblPrevMthTotals. These totals are currently not stored in a table;

    Rather,the user has to open the Report and a form (which the underlying table is "tblPrevMthTotals") and look at last mths values from the subReport and input those values in tblPrevMthTotals.

    To me, It seems redundant to have the user input values that are already available....so...I'm trying to come up with a way that, even though we shouldn't store TOTALS, would automatically update the tblPrevMthTotals once the report opens....

    For example....User opens the report, a delete qry runs to delete the 1 record in tblPrevMthTotals, and some sort of code would update tblPrevMthTotals with the TOTALS from the subRreport.

    Thanks in advance for any suggestions.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Make a Table w/Totals (Access 2k)

    Would it be possible to create an update query that calculates the sums and averages and updates the record in tblPrevMthTotals? You could then execute this query (using DoCmd.OpenQuery or CurrentDb.Execute) in the On Open event of the report. You wouldn't even need a delete query.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make a Table w/Totals (Access 2k)

    Hans,

    Unless I'm missing something (and that's entirely possible) I Don't think the option of running an update qry is doable.

    The subReport's underlying table is tblPrevMthTotals (meaning 1 record for both Table and report). Currently the Data for tblPrevMthTotals is input by the user.

    As I mentioned earlier, the way the data gets to the underlying table for the Report (tblPrevMthTotals) is, the user opens the report from the previous mth and a form to update tblPrevMthTotals. The user then takes the values from that report and manually inputs those values in the tbl. An update qry wouldn't have anything to update until the data is input in the table....which is exactly I'm trying to bypass.

    Thanks Hans,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Make a Table w/Totals (Access 2k)

    But the totals must have been calculated at some point - I don't assume that the user calculates the sums and averages by hand. It must be possible to store the results of those calculations in the table.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make a Table w/Totals (Access 2k)

    You are absoluetly correct!!!

    The values are calculated in the footer of the main report. These values are not bound to a field (as well as they shouldn't be), however, these values are used in the Next Month's report to show Previous Mths Totals.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Make a Table w/Totals (Access 2k)

    If it is possible to perform the calculations in the report footer, it should be possible to design a query that calculates the same results. This query could be used as the basis for an update query.

Posting Permissions

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