By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
5 Replies


Megalog
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
  1. =DSum("[Gross]","Table","[Model] = 'A'") 
The sql equivelant would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table.Model, Sum(Table.Gross) AS SumOfGross
  2. FROM Table
  3. GROUP BY Table.Model
  4. 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

Megalog
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
  1. Private Sub cboModelSelect_Change()
  2. dim strCtrlSrc
  3.  
  4. If nz(cboModelSelect,"") <> "" Then
  5.      strCtrlSrc = "=DSum($[Gross]$,$Table$,$[Model] = '$ & me.cboModelSelect.Value & $'$)"
  6. Else
  7.      strCtrlSrc = ""
  8. End If
  9.  
  10. Me.txtSum.ControlSource = Replace(strCtrlSrc,"$$","""")
  11.  
  12. End Sub
Jan 22 '09 #4

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.