count Record on a continuous form that summarizes groups primary value

[tagteam]

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

[June7]

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.