Totals with multiple criteria

[AndieV]

I need to create running totals for fields in a form based on multiple criteria.

I need the total kms and total litres that were entered on the current date, for the current unit number and month and the user needs be be able to either have a running total on the form or an autokey that would display the result.

Could someone help with what the best way to do this would be?

Thank you

Andie

 

[BoxHead]

Andie,

I'm reading the question two different ways, so I'll offer up two answers. Hope one of them fits your needs.

First, take a look at the DSum Function in Help.

DSum(expr, domain[, criteria])

An unbound textbox could have a control source something like:

= DSum("[kms]", "YourTableName", _
"[DateEntered] = Date() AND [UnitNumber] = " _ 
 & [Me].[UnitNumber])

Second, is the date entered an actual field in your table? or do you just want the users to be aware of the amounts they've entered in a session?

If Date Entered is not a field, you could add it to the table or add the amounts together as they are entered. You could have the control source of an unbound textbox set to

  = [TextName1] + [TextName2] + etc...

Either way, you'll have to requery this unbound textbox on the update event of the textboxes that are having the figures entered.

 txtTotalKms.Requery

If this isn't clear, please post back with a little more detail as to the layout of your form and the field names.



John

Use what you have,
Learn what you can,
Create what you need.

 

[AndieV]

Hi John,

Thanks for the tip. My original setup used DSum on an unbound text box but I didn't know how to integrate the months.

When the user enters data, they often enter two or three months at a time and they need the totals to be separate for each month and each unit number. If I base it on current date, then they get all months totals together, that's the part I'm having difficulty with.

Andie

 

[BoxHead]

Andie, I haven't had occasion to use the DatePart function, but you should be able to use it as the criteria in your DSum statement:

DatePart("m", [DateEntered]) = DatePart("m", Date())

should return only entries made during the current month.

Let me know if it works out.


HTH




John

Use what you have,
Learn what you can,
Create what you need.