Group By Query

[nols76]

Greetings. I am trying to run a query to count all records records within ranges in the previous month.

For example:
<TABLE style="WIDTH: 201pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=266><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 136pt; HEIGHT: 25.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=34 width=180 colSpan=2>Value </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=86>Count</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>0 - 100,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>100,001 - 500,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>500,001 - 1,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>1,000,001 - 2,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>2,000,001 - 3,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>3,000,001 - 4,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>4,000,001 - 5,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>5,000,001 - 6,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>6,000,001 - 7,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>7,000,001 - 8,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>8,000,001 - 9,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=18 colSpan=2>9,000,001 - 10,000,000</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>0</TD></TR></TBODY></TABLE>

So far I can come up with the prior month query:

SELECT PhysCertLog.[Market Value], PhysCertLog.MailDate, Count(PhysCertLog.ID) AS CountOfID
FROM PhysCertLog
HAVING (((Year([MailDate]))=Year(Now())) AND ((Month([MailDate]))=Month(Now())-1));

How can I then group them within the [Market Value] value ranges?

 

[tad0075]

You'll have to make a calculated value to group by, using some rounding. I would start by rounding up to the next million for values over 1,000,000. Possibly something like:

IIF([value]>1000000,round(([value]/1000000)+0.5),[value])

This will give you "2" for any values 1,000,001 - 2,000,000, "3" for any values 2,000,001 - 3,000,000, etc. You could always multiply by 1,000,000 again so the resulting value is meaningful. You won't have to show this value in your report or final query, just keep it available for grouping.

On the bottom 3 buckets, you'll probably have to just do a long IIF statement on the remaining values, like:

IIF([value]<=1000000,IIF([value]>500000,1000000,IIF([value]>100000,500000,100000),0),0)

This will give the top of the range for values 500,001-1,000,000, 100,001-500,000, and 0-100,000. For anything that doesn't fit these criteria (values over 1 mil), result will be 0.

You could even shove these 2 expressions together into 1 field, otherwise you'll have to combine them in your next query.

Does that work for you?

 

[rosegmeyer]

Additionally you can create a static table with two fields:

BEG_VALUE_______ END_VALUE
_________0________100,000
___100,001________200,000
___200,001________300,000 etc

You can then create a GROUPBY query using BETWEEN [BEG_VALUE] and [END_VALUE] in the field you are wanting to read the range value of. Then simply count the records.