426,222 Members | 1,129 Online
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Sum a Total Dollar amount within a specific group

 P: 58 Part of my Access Database has two fields that I want to work on: [Model] and [Gross] [Model] has a drop down list with ( "A", "B", "C", etc,) options. When I use the following expression, (in this case for Model A) Code. Abs(Sum([Table].[Gross] And ([Table].[Model]="A"))) AS [Total A Gross] I get the count of the number of records that the Model "A" has, which is 2. Instead, my intension is to get the total Gross amount of it, which is \$350.00. ModelGrossGross A A150.00...1 A.. 200.00...1 B..375.00.0 C..400.00......0 C..100.00...0 Total.2 What would it be the correct expression to get the Total Gross of each Model individual? Something like this would be the ideal Query: ModelGrossGross AGross BGross C A150.00..150.0..0 A. 200.00200.0..0 B.375.00..0375..0 C 400.00..0.0..400 C100.00..0.0..100 Total.350..375.500 If anyone can help me on this, will be greatly appreciated plaguna Jan 20 '09 #1
5 Replies

 Expert 100+ P: 378 Hrm, in an expression I know of only one way to do this.. there may be a better way but I'm not sure how to get to it. Here's my way, using your example data: Expand|Select|Wrap|Line Numbers =DSum("[Gross]","Table","[Model] = 'A'")  The sql equivelant would be: Expand|Select|Wrap|Line Numbers SELECT Table.Model, Sum(Table.Gross) AS SumOfGross FROM Table GROUP BY Table.Model HAVING (((Table.Model)="A")); Jan 20 '09 #2

 P: 58 I tried your code and, yes it works for Model "A" . Now I'm going to try adding Model "B" and "C" and .... I don't know the syntax but, I'll give it a try. Thank you plaguna Jan 22 '09 #3

 Expert 100+ P: 378 Just taking a wild guess at your scenario, but if this is being shown in one summary field, you could update the code to show whichever Model is being selected in a combo box, instead of hard coding a sum field for each model. cboModelSelect = combo box name, replace with yours txtSum = plain text field showing the Sum result Expand|Select|Wrap|Line Numbers Private Sub cboModelSelect_Change() dim strCtrlSrc   If nz(cboModelSelect,"") <> "" Then      strCtrlSrc = "=DSum(\$[Gross]\$,\$Table\$,\$[Model] = '\$ & me.cboModelSelect.Value & \$'\$)" Else      strCtrlSrc = "" End If   Me.txtSum.ControlSource = Replace(strCtrlSrc,"\$\$","""")   End Sub Jan 22 '09 #4

 Expert Mod 15k+ P: 31,419 Let us know if you're still struggling with this. It's hard to answer the question appropriately as it's unclear what is what and what you're trying to achieve and where. If you need more help we must start by clarifying the question so that we know (aren't just guessing) what we're dealing with. Jan 28 '09 #5

 P: 58 Megalog Something inspected happened at work that I couldn’t reply your answer. Sorry about that. Thank you for helping with the code you sent me. I’ll try it….. I’m sure It’ll work. Thanks again plaguna Feb 4 '09 #6