Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27

    Calculations on Report that use a parameter query to select year and period

    Database is two tables: tblContacts, tblMoney

    The goal is to track contributions over a period of years from a relatively small group (<500) of persons who may give more than once per year.

    tblContacts key is ContributorID
    tblMoney key is ContributionID and also has contributorID to link the tables.

    Contributions come in three forms: check, currency, and "inkind" (non-monetary)

    Summary report shows the total contributions per person for a specific year determined by a parameter query. So I run report, it asks for year and returns total of all contributions for that person/year. It shows the total checks, total currency, and total in-kind and then a yearly total for that contributor. This is really not needed as a separate report....it was just a trial run for the contacts report described below....which is the heart of my effort.

    Another trial run called Period report shows all contributions made during a specific period determined by another parameter query. I run report, it asks for beginning and ending date and returns the desired contributions by date....each on a separate line.

    MY PROBLEM IS TRYING TO COMBINE ALL THIS IN ONE REPORT......
    Contacts report shows each contribution for all contributors on a separate row based on date of contribution. I need to be able to specify the period of time covered by the contacts report (say Dec 10, 2014 to June 6, 2015 AND on the same report, I need to show the total contributions per contributor for the CALENDAR YEAR that I specify.



    So the perfect report may show JOHN DOE on three rows because he contributed on three dates...let's take this further and say that one contribution was a check, one was currency and one was "in-kind" but the yearly total column should show the same grand total for him out beside each contribution/row. And remember that all these individual contributions have to fall into the specified period AND the grand total has to be the calendar year I specify.

    I am willing to research this but am not coming up with good results in my internet searches....wondered if anyone can suggest proper wording for searches or point me in the right direction. Can this be done on ONE report?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you want the grand total on each of the detail records?

    You want the report to show a set of records filtered to a period less than a full year but still want to show the year-to-date total contributions?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    I'm working with a hard copy of the report information that is required by state government. The guy I talked to recently admitted that the close examination to which I've submitted these required reports does not support their layout. So, "why would I want the grand total on each of the detail records?" Because "the man" says it has to be that way. :-)

    Second question....yes, again because the hard copy report that I'm trying to replicate does it that way. I assure you that if I can get this DB running as I want, I'm going to meet with those who require the reports and suggest some changes that will make them work better in a database environment.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can do things like:

    1. build an aggregate query then join it to the detail table/query

    2. domain aggregate functions (DSum, DAvg, DCount, etc) expressions in query or textboxes

    3. subreports
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    One further clarification since I didn't quite address the question. The contribution reporting period required for the state report is arbitrary....it does not begin/end with months, quarter, etc. In fact it will often include parts of two calendar years. The period is not therefore related to a calendar year.

    Yet the same report requires me to submit totals by contributor for the CALENDAR YEAR since there are limits to contributions in a given year.

    So somehow, I have to filter all contributions for the period for one part of the report and I have to be able to sum all contributions for a calendar year on another section of this one-page hard copy report that I'm trying to replicate.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Still see only 3 options.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    Ok I'm trying this code in a textbox

    Yr = inputbox(“Enter the year”)
    Dsum(“[check] + [currency] + [inkind]”, money, “date1 = yr”

    where check, currency and inkind are field in a table called "money. Date1 is the date field in the money table. I think I need to have some reference to a textbox on the report too...am I even close?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have never seen InputBox() function used in a textbox ControlSource property. It really makes no sense to do that. Just have an UNBOUND textbox for users to input a value. Name the textbox like tbxYr.

    The date1 field has only year values?

    Then the DSum expression would be in another form textbox ControlSource:

    =DSum("[check] + [currency] + [inkind]", "money", "date1 = " & tbxYr)

    or in query or in textbox on report (don't forget the = sign in textbox):

    DSum("[check] + [currency] + [inkind]", "money", "date1 = " & Forms!formname!tbxYr)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    date1 is a full mmddyyyy date.....I have played around with using datepart to supply the yyyy for the part of this report that has to show the total contributions for the calendar year.

    Recall that I have to show both contributions for a defined period that is about six months and often includes parts of two calendar years AND I HAVE TO PROVIDE THE TOTAL CONTRIBUTIONS FOR THE CALENDAR YEAR OF THE ENDING DATE OF THE PERIOD.

    So if the period ends in December, the report for that period has to show all the contributors for the period (and their contributions) AND it has to show the total contributions for that whole calendar year.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, is the DSum working now?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    I have never seen InputBox() function used in a textbox ControlSource property. It really makes no sense to do that. Just have an UNBOUND textbox for users to input a value. Name the textbox like tbxYr. RESPONSE: I DID THIS
    The date1 field has only year values? RESPONSE: NO IT IS MMDDYYYY

    Then the DSum expression would be in another form textbox ControlSource:

    =DSum("[check] + [currency] + [inkind]", "money", "date1 = " & tbxYr) RESPONSE: I USED THIS LINE OF CODE IN ANOTHER TEXT BOX ON THE REPORT. IT DID NOT GIVE AN ERROR BUT I ENDED UP WITH TWO NEW BOXES ON THE REPORT. I PUT 2014 IN THE FIRST ONE...THE ONE YOU CALLED TBXYR AND IT DID NOT GIVE ME A TOTAL IN FACT BOTH BOXES WENT BLANK. WHAT I WANT TO HAPPEN IS FOR ONE BOX TO POP UP ASKING FOR A YEAR. USER PUTS 2014 OR WHATEVER IN THE BOX AND IT WILL TOTAL ALL CHECKS+CURRENCY+INKIND CONTRIBUTED BY EACH CONTRIBUTOR FOR THE YEAR ENTERED

    or in query or in textbox on report (don't forget the = sign in textbox):

    DSum("[check] + [currency] + [inkind]", "money", "date1 = " & Forms!formname!tbxYr)

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    None of your posted attempts show use of DatePart or Year functions in the DSum. That's why I asked about the content of date1 field.

    DSum("[check] + [currency] + [inkind]", "money", "Year(date1) = " & Forms!formname!tbxYr)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    Not sure why you use " & Forms!formname!tbxYr?

    I have a form that I use for data entry composed of a "contacts" section and a subform into which the user enters the contribution details that go into the "money" table. This is the only form I'm using. Are you suggesting that I should be using a form instead of or as part of my report?

    I have been working to use the code you've suggested in a text box that is in my report. So if I'm working only with the report, how do I reference the form in forms!formname!tbxYr
    Last edited by Hardrock; 01-23-2015 at 10:20 PM. Reason: removed unnecessary text

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I never use parameter input popups. Can't validate user input. If they enter bad value, the query will still try to run but results will be unacceptable and just causes frustration.

    I did test your idea for a popup in the DSum expression in a textbox on report and it does work, but has the risk stated above.

    =DSum("[check] + [currency] + [inkind]", "money", "Year(date1) = " & [enter year])

    I am suggesting, and this is common practice, that you use a form for user input of filter criteria. User input can be validated before opening report. The textbox used to input filter parameter must be UNBOUND. It can be on your one data entry form if you want - in the form header. Then I presume you have a button for "Open Report" also on this form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    Hardrock is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Posts
    27
    OK, I want to learn this the right way so I'll go with the form. I don't know how you're compensated for all this help but I really appreciate it.

    So are you saying that my existing input form can also be used for both my date filter criteria needs? I have to have user put in beginning and ending dates (mmddyyyy) to get the data for the period that we've discussed above (recall that I need all contributors and contributions for an approximate six month period that may include two calendar years AND IN ADDITION the user needs to enter a calendar year for which we're going to retrieve all contributions for the calendar year user enters. All this can be done on one "criteria" form that will then call the report and show all this data. Are we on the same page?

    Final question....does the code you have provided work the same on the form or am I doing something different on the form?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-17-2014, 05:19 AM
  2. Replies: 4
    Last Post: 01-25-2013, 05:20 AM
  3. use parameter in field name of select query
    By focosi in forum Queries
    Replies: 2
    Last Post: 02-11-2012, 10:34 AM
  4. Parameter Query Calculations
    By mrk7891 in forum Queries
    Replies: 2
    Last Post: 11-04-2011, 04:33 PM
  5. Parameter Query: Select From A List?
    By catbob in forum Queries
    Replies: 4
    Last Post: 02-08-2010, 08:24 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