Results 1 to 2 of 2
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    494

    count Record on a continuous form that summarizes groups primary value


    I have a continuous form that shows all the available lease numbers for a client. Each lease number has a monthly charge associated with it and has multiple cars associated with it. For example a client may have lease number 1, 2.5, and 3. Lease 1 may have 3 cars, lease 2 10 cars and lease 3 1 car.

    I created a continuous form that shows each individual lease number for a client and the monthly charge. The form is based on a totals query where I use a count to get the total number of cars for the lease number. That all works fine and when i open the form it shows me all the lease numbers for the active client and the monthly charge like the table below.

    Lease number Monthly Charge Total cars in lease total value of lease value left on lease
    1 100 3 100x3=300
    2.5 200 10 200x3=600
    3 250 1 250x3=750



    My problem is I am trying to do some calculations with it and is not working right

    I am trying to do =Sum([CRevFromCustomer]*[CountOfLeaseNumber]) but it gives me the same value for all lease numbers. It gives me the total value of all the lease numbers for that client instead of showing me the total value each individual lease number. table below shows what I mean where 1650 is the total from the 3 different lease numbers and revenue added together.

    Lease number Monthly Charge Total cars in lease total value of lease value left on lease
    1 100 3 1650
    2.5 200 10 1650
    3 250 1 1650

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Aggregate calcs by groups is not easy on forms. Usually have to resort to domain aggregate functions (DSum, DAvg, DCount, etc).

    Reports are designed to handle aggregate calcs by groups.
    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. More than One Record on filtered Continuous Form
    By Williams485 in forum Forms
    Replies: 3
    Last Post: 03-13-2013, 04:56 AM
  2. Replies: 3
    Last Post: 10-12-2012, 01:38 PM
  3. Auto Count Month in Continuous Form
    By Mpike926 in forum Forms
    Replies: 10
    Last Post: 05-18-2012, 11:18 AM
  4. Hiding a new record in a continuous form
    By system243trd in forum Forms
    Replies: 3
    Last Post: 12-03-2011, 01:04 AM
  5. Get position of record in continuous form
    By Whizbang in forum Forms
    Replies: 3
    Last Post: 11-02-2011, 01:47 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