Break number entries into ranges and count against group

[CCousins]

Almost zero SQL knowledge and limited Access experience.

Developing queries from table based on survey responses. A couple of fields
are: TownDist (distance the respondent lives from town) and Income (stated
household income). The topic of ranges didn't come up until after the data
had been entered. I've developed corresponding tables: TownDistRange and
IncomeRange, each with four fields: ID, Range (text description), Min, Max.
And I've entered the info to identify each range.

I am trying to develop a query to, a) group towns by name, b)count how far
respondents live from each town, by range, and c)count income ranges
responding to each town.

I've tried doctoring a couple of SQL statements I found by searching here,
having to do with monetary ranges, but to no avail.

Any help would be greatly appreciated. Thanks.

 

[[MVP] S.Clark]

Here's a stab at it...

Select TownName, Range, Count(Min), Count(Max) from TownDistRange
GROUP BY TownName, Range

Let us know where this query goes wrong and we'll back into what you need.

 

[CCousins]

SELECT TransSurv2006.Town, TblTownDistBands.DistBand,
Count(TblTownDistBands.MinNumber) AS CountOfMinNumber,
Count(TblTownDistBands.MaxNumber) AS CountOfMaxNumber
FROM TransSurv2006, TblTownDistBands
GROUP BY TransSurv2006.Town, TblTownDistBands.DistBand;

Thanks for the direction to head in, Steve! I modified your suggestion a
bit to reflect the actual table and field names, and pasted the result above.


The output lists town (once for each DistBand(range)), the text for ranges,
but then two columns of counts as follows:

Town DistBand CountOfMinNumber CountOfMaxNumber
arden 0 miles 1
1
arden 1 to 5 miles 1 1
arden 6 to 10miles 1 1
arden Over 10miles 1 1
Colbert 0 miles 1 1
Colbert 1 to 5 miles 1 1
Colbert 6 to 10miles 1 1
Colbert Over 10miles 1 1
Curlew 0 miles 1 1
Curlew 1 to 5 miles 1 1
Curlew 6 to 10miles 1 1
Curlew Over 10miles 1 1
Ford 0 miles 2 2
Ford 1 to 5 miles 2 2
Ford 6 to 10miles 2 2
Ford Over 10miles 2 2
Metaline Falls 0 miles 1 1
Metaline Falls 1 to 5 miles 1 1
Metaline Falls 6 to 10miles 1 1
Metaline Falls Over 10miles 1 1

From a test source table where there is one entry each for towns except
Ford, with 2 entries. As you see the counts are filling in all ranges at the
number of responses for each town rather than the number of responses per
range.

I can smell the blood, but we haven't made the kill, yet Thanks for your
help,

Charlie Cousins
Rural Resources
Colville, WA

 

[CCousins]

Just noted that we didn't address the respondent's entry: TownDist in order
to compare it to the min/max to arrive at a range

 

[[MVP] S.Clark]

This query results in a cross join, as there is no explicit join between the
two tables. Note the addition below.

SELECT TransSurv2006.Town, TblTownDistBands.DistBand,
Count(TblTownDistBands.MinNumber) AS CountOfMinNumber,
Count(TblTownDistBands.MaxNumber) AS CountOfMaxNumber
FROM TransSurv2006, TblTownDistBands

INNER JOIN on TransSurv2006.[SomeIDField] ON
TblTownDistBands.[MatchingIDField]

GROUP BY TransSurv2006.Town, TblTownDistBands.DistBand;