Help with Calculated Field

[Sam]

I have a Billing table with the following fields - BillID(PK), BookingID(FK), BillNo(text), BillPercent(Number) and BillDetails(text).



I need to put in a calculated field after BillPercent, which will give me a sum of the previous BillPercents for the BookingID.

What should the expression be like ?

[June7]

What do you mean by sum? BookingID will have multiple records? How is BillPercent derived?

It is usually a bad idea to save calculated data, especially aggregate calc. Calculate when needed.

Looks to me like should probably build a report and do aggregate calculations there. Maybe even use textbox RunningSum property.

[Sam]

To clarify I have attached an image of a sample.
Let's say I now want to add a new record, which would be BillID 5, for BookingID 1, I need the calculated field to sum the previous entries in BillPercent (1 and 3) for BookingID 1

I hope I am clear. In layman language I want the previously billed percentage for this person.
Your suggestion of the RunningSum sounds right. How do I go about doing that ?

[June7]

Build a report. Textbox RunningSum property is only available on report. Use report Sorting & Grouping features as well.

You would enter record and commit to table then run report.

Calculating a sum for selective records on form is possible with DSum() function in form header or footer section.

=DSum("BillPercent","Billing","BookingID=" & [BookingID])

However, if filter is applied to form, this calculation might not return correct result. Also, it will not reflect new record entered until record is committed to table.

[Sam]

=DSum("BillPercent","Billing","BookingID=" & [BookingID])

I used this in a query and it works fine giving me the sum of BillPercent for that BookingID. But I still need to set the additional criteria to sum all BillPercent in the previous records up to current record.

In other words I need to Sum all BillPercent for a particular BookingID where BillID is less than current BillID.

[June7]

That still sounds like a running sum calculation. Methods to accomplish:

1. nested subquery

2. VBA custom function

3. DSum("BillPercent", "Billing", "BookingID = " & [BookingID] & " AND ID<=" & [ID])
Or use BillDate instead of ID in the criteria: " AND BillDate<=#" & [BillDate] & "#"
Since your data shows international date format, suggest you review http://allenbrowne.com/ser-36.html

4. report with textbox RunningSum property


Review https://www.tek-tips.com/viewthread.cfm?qid=1532770

[Sam]

Thanks a ton June. Got it working.