Results 1 to 4 of 4
  1. #1
    A kinason is offline Novice
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    23

    Calculating balances based on amounts in a table


    i have a db whic permit to Admit students, receive fee (advance or tatal fee). Each student has a contract value which is stored in the table "Contract". this table comprises: ContractID, ContractValue and StudentID.

    the amount that is paid by a student is stored in the table "Finace". Knowing that a student in a bit to complete the contract value can pay many times, i have a problem calculaing the balance automatically using Access.

    So i want to be able to calculate the balance of student fee based on the contract value and the amount the sudent has already paid. Is this possilble in Access 2007?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use aggregate query to sum the payments by student, join the aggregate query to the Contract table, calculate difference between the advance and the sum of payments.

    Another approach is to just do a join between the two tables and build report with Grouping and Sorting features and aggregate calcs in group section. This allows display of detail records as well as summary data.
    Last edited by June7; 05-09-2013 at 10:19 PM.
    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
    A kinason is offline Novice
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    23
    Thanks alot June7

    I have used a crosstab query to get the sum of payments, then a simple query of the "crosstab query and the table Cotract" to have the balance. I hope this will have no future inconviniences?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Building reports based on CROSSTAB query to run perpetually can be tricky because they can be so dynamic. If an expense has no costs for a report period then the textbox on report has no field to bind to.
    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.

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

Similar Threads

  1. Fee Calculation with Previous balances
    By jalals in forum Programming
    Replies: 14
    Last Post: 04-26-2013, 11:06 AM
  2. Replies: 10
    Last Post: 12-05-2012, 04:02 PM
  3. Replies: 3
    Last Post: 05-11-2011, 02:32 PM
  4. Beginning and balances in Access 2007
    By jalovingood in forum Access
    Replies: 1
    Last Post: 04-19-2011, 03:34 PM
  5. Calculating values based on Combobox value
    By BrianFawcett in forum Programming
    Replies: 1
    Last Post: 05-06-2010, 01:26 AM

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