SUM used on filtered records

[Mantispony]

Hi, I thought I knew enough about Access until I took a look in here, lol.

(Feel free to move or delete this thread if appropriate)

My challenge is this:

I've got a list of customers and their sales values, as below:
custID | custName | custGroup | custValue

The custGroup field is where we keep track of who handles that customer regularly. I'd like an additional field CustTotalValue to display the sum of that person's group's sales.

Bottom line: When I filter on group 3, I'd like CustTotalValue to display the sum of custValue for group 3.

I'm not VBA fluent, so if this solution requires VBA; please pretend you're explaining it to a 3-year-old

Many thanks

 

[Trevor G]

Have you looked at using the DSUM via the expression builder.

DSum («expr», «domain», «criteria»)

Suggestion create a form from the table, then add an extra Texbox to the form, and then use the DSUM function in the extra text box, you can then look to do something like filter the form to show the person group sales.

 

[Mantispony]

..ok so I've got this: DSum (+, TOTAAL2011, «criteria») but I don't know what to put for the criteria. (and I'm not sure about the + either lol, I only have a VERY basic understanding of functions)

"TOTAAL2011" is the field where the total sales for 2011 are stored. Does the "group" field go in criteria?

Thanks ever so much!

 

[Trevor G]

Take a look at this YouTube video it will show you and you can keep replaying it until you have it working.

http://www.youtube.com/watch?v=fLYZju_NrPw

There are plenty of others there 2

 

[Mantispony]

Oh many thanks! That helps a lot!

 

[Trevor G]

Pleased to read it helps you.

 

[Apolo13]

Can I see the YouTube's clip?
I clicked on the link, but it not work...

 

[GregAcc]

Video doesn't work.

Take a look at this YouTube video it will show you and you can keep replaying it until you have it working.

There are plenty of others there 2

 

[Gasman]

So look for others?

https://www.youtube.com/results?search_query=vba+dsum+multiple+criteria

 

[Pat Hartman]

Assuming this is a report or a form, create a new control. The ControlSource will be something like:

=dSum("custValue", "yourtablename", " custGroup = " & custGroup)

Change "yourtablename" to the name of your table. The other field names should be ok. If the group is a string, you will need to use the following instead:

=dSum("custValue", "yourtablename", " custGroup = '" & custGroup) & "'"