To combine and sum in a query

[mdnikki]

So I have an access report I have created that shows a summary of donors and their appeal codes. To do this I used a query that brought all of our donors and their appeal codes, then summed them by appeal code to get what is shown below.

CountOfAppeal Description	SumOfGift Amount	Appeal Description	percent	avggift	# solicited
10849	$2,486,744.03	12AAA 1stMail	0.413594601807022	229.214123882385	26231
40	$5,150.00	12AAA 1stMail - New Adds	6.39795265515035E-03	128.75	6252
831	$122,083.24	12AAA 1stMail Sybunt	4.60132890365448E-02	146.911239470517	18060
1747	$314,923.90	12AAA 2ndMail	8.40065397191768E-02	180.265540927304	20796
644	$87,886.33	12AAA 3rdMail	0.031078081266287	136.469456521739	20722
234	$957,512.74	12AAA Direct Solicitation		4091.93478632479
1	$1,200.00	12AAA In House Phone		1200
15839	$3,064,722.69	12AAA InPew		193.492183218638
439	$187,220.05	12AAA Internet		426.469362186788
35	$20,606.14	12AAA Payroll		588.746857142857
1278	$210,223.00	12AAA Phone		164.493740219092

However now they would like to see this summarized further. What we would like to do now is summarize all the appeal descriptions that are "mail" so that we can see how much came in via 1st mail, 2nd mail combined. So I need to combine all the appeals with Mail in the description to see this.

CountOfAppeal Description SumOfGift Amount Appeal Description percent avg gift # solicited xxxxx xxxxx Direct Mail (this would combine all the "mail appeal codes") xxxx xx xx xxxxx xxxxx Phone (this would combing 12AAA in house phone and 12AAA Phone) xxxx xx xx xxxxx Direct Solicit xxxx xx xx xxxx xxxxx Other (this would be 12AAA Internet, 12AAA Payroll, 12AAA in Pew) xxxx xx xx
Any help is appreciated. Thanks

[alansidman]

Attached is a potential solution

[June7]

Need a field with mail categories to base grouping on. If you don't have a native field in table for this value, might be able to calculate it in query with IIf expression:

IIf([Appeal Description] Like "*Mail*", "Direct Mail", IIf([Appeal Description] Like "*Phone*", "Phone", IIf([Appeal Description] Like "*Solicit*", "Direct Solicit", "Other")))

[rpeare]

I would set this up in your query running the report. Create another like:

AppealGroup: iif(instr([appeal description], "1stMail")>0, "1st Mailing", iif(instr([appeal description], "2ndMail") > 0, "2nd Mailing", "Everything Else"))

Where you'd just do a nested if for each one of your groups. This method would not require you to update any of your tables.

Then create a sort/group on your report of this appeal group and do subtotals in the group footer.

However what I'd suggest is that you make a table (tblAppealGroups?) that stores a PK and an appeal group and make that part of your regular data entry then you don't have to mess with nested ifs and it's far more flexible for future use than if you hard code reasons.

[mdnikki]

What if I put the actual type of appeal in the original table. Will that make it easier to group..I want to group and sum by the mail type.

CountOfAppeal Description	SumOfGift Amount	Appeal Description	percent	avggift	# solicited	Type
10849	$2,486,744.03	12AAA 1stMail	0.413594601807022	229.214123882385	26231	Direct Mail
40	$5,150.00	12AAA 1stMail - New Adds	6.39795265515035E-03	128.75	6252	Direct Mail
831	$122,083.24	12AAA 1stMail Sybunt	4.60132890365448E-02	146.911239470517	18060	Direct Mail
1747	$314,923.90	12AAA 2ndMail	8.40065397191768E-02	180.265540927304	20796	Direct Mail
644	$87,886.33	12AAA 3rdMail	0.031078081266287	136.469456521739	20722	Direct Mail
234	$957,512.74	12AAA Direct Solicitation		4091.93478632479		Direct Solicitaion
1	$1,200.00	12AAA In House Phone		1200		Phone
15839	$3,064,722.69	12AAA InPew		193.492183218638		Other
439	$187,220.05	12AAA Internet		426.469362186788		Other
35	$20,606.14	12AAA Payroll		588.746857142857		Other
1278	$210,223.00	12AAA Phone		164.493740219092		Phone

[June7]

That is what we suggested.

Did you try the query?

[mdnikki]

I am not sure how to group once they all are assigned a mail "type".
I did an update query to get my original table to have this field Type for all records.
Now I am trying to run a query to summarize by type and I just can't seem to figure out how to do it.

[mdnikki]

I think i just figured it out..thx