Sorting Multiple Entries

[Tarvos{k}]

Okay folks,

I am back with another somewhat weird problem. I have a table that I am
trying to run a query on that has the following fields: City, State, Zip
Code, Population, Households.

The info for the table came from US Census information for 2000, and what I
need is to find the number of times a particular entry repeats. Example: I
want to find the number of times a city name shows up, and the number of
states it shows up in.

I am trying to do this all through the design view in Access, though I would
be willing to try the SQL if someone has a suggestion.

Tarvos{k}

[pietlinden]

Do you mean this?

SELECT tblCityState.City, Count(tblCityState.State) AS CountOfState
FROM tblCityState
GROUP BY tblCityState.City;
Or did you want to list the states as well? Or what did you mean by
"entry"? A family?

[jimfortune]

Tarvos{k} wrote:

Okay folks,

I am back with another somewhat weird problem. I have a table that I am trying to run a query on that has the following fields: City, State, Zip Code, Population, Households.

The info for the table came from US Census information for 2000, and what I need is to find the number of times a particular entry repeats. Example: I want to find the number of times a city name shows up, and the number of states it shows up in.

I am trying to do this all through the design view in Access, though I would be willing to try the SQL if someone has a suggestion.

Tarvos{k}

I have a SQL suggestion!

tblCensusInfo:
ID AutoNumber
City Text
State Text

1 Salem OR
2 Salem OR
3 Salem OR
4 Salem MA
5 Salem MA
6 Salem NH
7 Amherst NY
8 Amherst MA
9 Amherst OH

tblCensusInfo_Crosstab:
TRANSFORM Count([ID]) AS [The Value] SELECT [City], Count([ID]) AS
[ShowsUp], Count([The Value]) AS NumberOfStates FROM tblCensusInfo
GROUP BY [City] PIVOT [State];

gave:

City ShowsUp NumberOfStates MA NH NY OH OR
Amherst 3 3 1 1 1
Salem 6 3 2 1 3

You could write a second query based on this one that grabs just the
first three fields. Disclaimer: I've never played around with crosstab
queries before so be sure to test this method out thoroughly.

James A. Fortune