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@hotmail.com]

Re: Sorting Multiple Entries

Do you mean this?

SELECT tblCityState.Ci ty, Count(tblCitySt ate.State) AS CountOfState
FROM tblCityState
GROUP BY tblCityState.Ci ty;


Or did you want to list the states as well? Or what did you mean by
"entry"? A family?

[jimfortune@compumarc.com]

Re: Sorting Multiple Entries

Tarvos{k} wrote:[color=blue]
> Okay folks,
>
> I am back with another somewhat weird problem. I have a table that I[/color]
am[color=blue]
> trying to run a query on that has the following fields: City, State,[/color]
Zip[color=blue]
> Code, Population, Households.
>
> The info for the table came from US Census information for 2000, and[/color]
what I[color=blue]
> need is to find the number of times a particular entry repeats.[/color]
Example: I[color=blue]
> want to find the number of times a city name shows up, and the number[/color]
of[color=blue]
> states it shows up in.
>
> I am trying to do this all through the design view in Access, though[/color]
I would[color=blue]
> be willing to try the SQL if someone has a suggestion.
>
> Tarvos{k}[/color]

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_C rosstab:
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