Sum Query Exception

[jlwmkkp]

I have created a basic query summing account balances by unique names.

I need to create an exception where two particular names (ie. "John" and "Jim") are added together.

How would I implement this?

Thanks,

jlwmkkp

 

[plog]

We need more data. Specifically, what do you have so far, what are the relevant field names. And do you mean instead of 2 lines in the query, one saying 'John' and another saying 'Jim' you want one record that says "John/Jim".

With that said, I'll take a stab at it with these assumptions: You have a working query that gives you balances by name, but John and Jim each have their own record and the name field in your table is called [DataName].

Where you have [DataName] in your query now, replace it with this:

DataGrouping: Iif([DataName]="John" OR [DataName]="Jim", "John/Jim", [DataName]

That should do it if all my assumptions are correct. If not, answer my intial paragraph's questions.

 

[jlwmkkp]

Plog,

Thanks so much. Most of your assumptions are correct. I'm using Access 2003, and have described the problem more in depth below.

The name field is called [NameField], and I am trying to consolidate two records (Jim, John) into one record.

For the purpose of this query, I'd like to name both of the fields (Jim).

I have added
Iif([NameField]="Jim" OR [NameField]="John","Jim",[NameField])
as a criteria, but when in Datasheet view, the Account Balance field only displays the value for "Jim".

Do you have an idea about what may be preventing the query from functioning?

Thanks,
jlwmkkp

 

[plog]

field only displays the value for "Jim".

Because that's exactly what you told it to do. If the name was John or Jim, you told it to show "Jim", otherwise it was to show the value in NameField. The total besides the row that says "Jim" should sum to the correct value.

 

[jlwmkkp]

Plog,

Apologies. I misspoke. The sum field does not display the correct sum. It only shows Jim's original account balance; it does not show the sum of Jim and John.

Best,
jlwmkkp

 

[plog]

Time to post SQL.

 

[jlwmkkp]

SELECT tbl_TableName.[DataName], Sum(tbl_AccountBalance.[Net Fee Accrual]) AS [SumOfAccountBalance]
FROM tbl_TableName
GROUP BY tbl_TableName.[DataName]
HAVING (((tbl_TableName.[DataName])=IIf([DataName]="Jim" Or [DataName]="John","Jim",[DataName])));

 

[plog]

This SQL is what you need:

SELECT IIf([DataName]="Jim" Or [DataName]="John","Jim/John",[DataName]) AS DataGrouping, Sum(tbl_AccountBalance.[Net Fee Accrual]) AS [SumOfAccountBalance]
FROM tbl_TableName
GROUP BY IIf([DataName]="Jim" Or [DataName]="John","Jim/John",[DataName]);