Groupings

  • Thread starter Thread starter Charles P. \(Pat\) Upshaw
  • Start date Start date
C

Charles P. \(Pat\) Upshaw

Could someone please tell me how to query names and amounts into groups.
This is for church donations. How many gave in each dollar group. For
example: 20 gave over $5000, 35 gave $3000-$4999, 45 gave $1000-$2999.

Thanks,
Charles P. (Pat) Upshaw
 
It's a serious hack...I think but try:

SUM(Amount > 5000) as FiveK, SUM(Amount < 5000 AND Amount
3000) As ThreeK,....
Click to expand...

The Amount > 5000 is a bitwise -1 for true so the SUM()'s
add up the # of records that match the appropriate
categories...the crappy part about my hack is you have to
manually create the brackets.

Cheers,

Eric
 
Hi,
Create a new query. Fill as many columns as needed
with variations of the following statement. For your
example, you would have 3 columns in the query. Type
something like the following into each field box:

Btw1000and2999 :IIf(amtfield>=1000 And
amtfield<=2999,1,0)

This will create a field called Btw1000and2999. If your
amount field is between 1000 and 2999 dollars,
Btw1000and2999 is assigned a 1, otherwise it is assigned a
0. When you have created a Field for all your different
donation groups, click on the totals button. Change all
Group By in Total line to SUM. This will give you counts
of how many donations fall into each category.
 
Hi,



SELECT COUNT(*),
Switch( Amount>5000, "5000+",
Amount>=3000, "3000-4999",
Amount >=1000, "1000-2999",
TRUE, "Other")
FROM myData
GROUP BY Switch( Amount>5000, "5000+",
Amount>=3000, "3000-4999",
Amount >=1000, "1000-2999" ,
TRUE, "Other")




Alternatively, fill the limits into a table:


Limits ' table name
FromThis, ToThis 'fields
0 1000
1000 3000
3000 5000
5000 999999999 ' data


and then


SELECT FromThis, ToThis, COUNT(*)
FROM myData INNER JOIN Limits
ON (myData.Amount>=Limits.FromThis )
AND (myData.Amount < Limits.ToThis)

GROUP BY FromThis, ToThis




The advantage of this last one is that you can modify the limits without
touching the SQL code.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top