Results 1 to 6 of 6
  1. #1
    bobbito is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2016
    Posts
    11

    Report with User-Defined Paramters passing data to Calculated Fields

    Access 2007

    Hello All

    I am trying to create a report that will show (1) Starting Loan Balance (2) Activity for a period the user has defined and (3) Ending Loan Balance.

    I have a table I'm calling tblLoanDetals that has the Original Principal Balance stored for each loan.

    There is a sub table that is storing all the activity details for each loan, this table is called tblRollForward. This table has the date and amount of each activity. So you might imagine a payment was made on the table on 7/4/2015 and more money was borrowed on 1/15/2016. This table would have a date column populated with 7/4/2015 and the Payment column populated with -50000... then on another line, date would be 1/15/16 and another column called "New Issuance" might be 100000.

    I then have a query qryRollForwardTotals which sums all the activity by column. So there is a Sum of Payments column and a Sum of New Issuance column.



    When my user attempts to generate a report I want them to enter a Starting Date and Ending Date. Then, the database needs to net all the activity up to the starting date against the Original Principal Balance, this will give me a "Starting Balance" that is specific to the period the user has requested. Then the database needs to net all the activity up to the ending date against the Original Principal Balance, this will give an "Ending Balance" that is specific to the requested period.

    Right now, my report generates but its basically generating from the aforementioned query and its showing just the "grand sums" of each activity type. I don't know how to allow the user to enter parameters, let alone, perform these sort of cross-column calculations. This is my very first database, thank you in advance for your help!

    I've already tried to use an expression and it only "sort of" worked... if there were any blank fields on the tblRollForward then the expression would not calculate.

    I also would like to display each loan, even if there was no activity on it, so in that case, just showing the Starting and Ending Balances.

    (BTW, I tried uploading the .accdb file but it failed to do so no matter which computer I tried from, I think maybe there is a problem with the site?)
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can make a form, say, frmRpts.
    on it has text boxes, named : txtStartDate and txtEndDate

    the query will use this as critieria....(but use the whole path to it)
    select * from table where [date] between forms!frmRpts!txtStartDate and forms!frmRpts!txtEndDate

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well, it looks to me like the table "tblRollForward" is designed like a spreadsheet. (not good)

    The field names "StartBalance", "NewIssuance", "PrincipalPymts", "RevolverNet", "FX", "InterestRateSwapMTM", "OtherChanges" are actually data.
    "EndBalance" can be calculated so it shouldn't be stored.


    I might have a table structure:

    tblRollForward
    --------------

    TransID (PK)
    LoanID (FK)
    TransDate
    Amount
    Action (FK to tblActions)


    I obviously don't know all of your requirements so this is just a very rough suggestion...........

  4. #4
    bobbito is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2016
    Posts
    11
    cleanrollfwd.zipHi ssanfu

    Would that table structure allow me to still report each activity type as its own column? This is a requirement. You may notice in the database Loan Activity > Enter New Loan Activity that there are multiple entries for each quarter (1/28/16 = 1,000,000, 2/28/16 = 1,000,000). This report is run quarterly and I do not want to report those entries individually, instead, they should appear as a single $2,000,000 entry under "New Issuance" on the report. From there, that $2,000,000 needs to net against all the other activity and the "starting balance" to produce an "ending balance."

    I did actually experiment with your table structure before I got to this point, but if you can show me a way where I can calculate and print "Action" totals by quarter AND have that total included in a "netting" calculation to produce an ending balance, please, SHOW ME THE WAY!

    Attached is a copy of the report as it is currently sent out.

  5. #5
    bobbito is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2016
    Posts
    11
    Hi ranman,

    I kinda already have that, its currently an "unrelated object" called frmRollForward but idk where to make those edits you're suggesting. Please help?

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would that table structure allow me to still report each activity type as its own column?
    Yes, but it will probably take more code and queries and maybe a temp table. I'm just saying I don't think the table structure is correct for a RDBMS.

    Then again, maybe your way is the better way....I just don't have the time to develop another dB. Sorry.

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

Similar Threads

  1. User-Defined Text in a Report???
    By tanyalee123 in forum Reports
    Replies: 1
    Last Post: 12-22-2014, 05:06 PM
  2. User defined columns report
    By Deutz in forum Reports
    Replies: 2
    Last Post: 10-28-2014, 06:39 PM
  3. Replies: 13
    Last Post: 12-12-2013, 07:22 PM
  4. Non-Saved user defined fields for reports
    By mrmims in forum Reports
    Replies: 3
    Last Post: 11-30-2013, 05:10 PM
  5. Replies: 3
    Last Post: 11-12-2013, 04:13 PM

Tags for this Thread

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