Count on field values 3

[oaklandar]

I have a table where the data looks like this in my Access 2000 database:

ID              State
1               Florida, Georgia, Texas, Oregon
2               Texas, Florida
3               Vermont, Texas
4               Oregon, Georgia
5               Georgia, Texas, Florida
6               Florida

I am trying to get a count of how many times each state shows up in the database.
I should get a count such as:

Florida = 4
Georgia = 3
Texas = 4
Vermont = 1
Oregon = 2

I have tried many sql attempts but not working.

select state, count(*) as myState
from myTable
group by state

Please advise how I can do this?

 

[lespaul]

Your problem is that this data is not normalized. (Normalized - One record contains information about one thing) your table has a single record that contains information about more than one thing.

What version of Access are you using?


Leslie

 

[oaklandar]

Access 2000 database.

I guess I cant do this query as you suggested?

 

[lespaul]

Access 2000 has a function SPLIT that you might be able to use to split up your multi state fields. Otherwise, you are probably going to have to write some kind of function to count this for you. There are several threads that have examples, I'll post links if I can find them.


Leslie

 

[Golom]

Some creativity is required. You will need to run a query that parses the individual State names into separate records and then run your count-query on the result. Something like this

Select T.StateName, Count(*) As [StateCount]

From 

(

SELECT Mid$("," & I1.State & ",", S1.num + 1 , S2.num - S1.num - 1) AS [StateName]

FROM myTableAS I1, Integers AS S1, Integers AS S2

WHERE 
S1.num <= Len("," & I1.State & ",") 
AND S2.num <= Len("," & I1.State & ",") 
AND Mid$("," & I1.State & "," ,S1.num , 1 ) = ','
AND Mid$("," & I1.State & "," ,S2.num , 1 ) = ','
AND S1.num+1 < S2.num
AND Instr(1,Mid$("," & I1.State & ",", S1.num + 1 , S2.num - S1.num - 1),",")=0

) As T

GROUP BY T.State

Where "Integers" is a table containing a "num" field with records from 1 to two more than the maximum length of the longest string in "myTable.State".

 

[Golom]

Correction ...

That should be GROUP BY T.StateName

 

[lespaul]

Ok Golom, that's the second thread I've seen you use that logic on. Did you just learn that technique? What exactly is it doing? So, Integers is a table that has a single field (NUM) and each record is like:

1
2
3
4

and you need 1 - 2 records more than the max length of the field? So, in this case there would need to be more than 31 records (since the longest string in the example above has 31 characters)? Then what?

les

 

[dhookom]

Create a table of all states (tblStates)
[TT]
[StateName]
===========
Florida
Georgia
Oregon
Texas
Vermont
[/tt]

Then create a simple query
SELECT StateName, Count(StateName) AS CountOfStateName
FROM MyTable, tblStates
WHERE InStr([state],[StateName])>0
GROUP BY StateName;

Result:
[tt][blue]
StateName CountOfStateName
Florida 4
Georgia 3
Oregon 2
Texas 4
Vermont 1[/blue]
[/tt]

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Golom]

Duane has a point. His solution is probably preferable ( ... well actually, normalizing the database is preferable ... but anyway ...).

Duane's solution works well and has the not insignificant benefit that it's easier to understand than the on-the-fly parsing that I suggested. It's only failing is that you need to be drawing from a known subset of "legal" values such as the names of the states as in this example. The other possible "gotcha" is someone abbreviating or misspelling a state name.

Leslie

No. I didn't just learn it. I've been using it for some time because I encounter a fair number of databases that use a comma-delimited format to stash multiple values in a field. I originally picked it up from Rudy Limeback's site (

http://www.r937.com)

and I think that Rudy got it from something that Joe Celko published.

Anyway ...

Here's what's happening

Start with a,b as the string that we are processing.

The query converts that to ,a,b, by prepending and appending commas. "Integers" will have the values 1-5 which is the length of ,a,b,

We select substrings such that the first and last characters are commas (i.e. something like a, is eliminated because it doesn't start with a comma.)

We also eliminate all strings where the first character is after the last character (i.e. S1.Num must always be less than S2.Num)

Finally we drop all substrings that have an embedded comma. In this example a,b is eliminated because of the comma in the middle.

We are left with two substrings "a" and "b" from the original 25 (1 * 5 * 5) created by the cross join.

Hope that unmuddies the waters at least a bit.

 

[lespaul]

That's pretty cool, thanks for sharing!

les

 

[dhookom]

There is another issue with my solution. It may not work properly with states like:
[blue] Virginia - West Virginia
Texas - South Texas[/blue]

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[lespaul]

South Texas - Is that where all the Texans were sent who didn't vote for Bush?



Leslie

 

[dhookom]

Leslie,
Sounds about right (or is that left)?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[oaklandar]

thanks that works, now I just need some explanation on how this works?

I am not familiar with what InStr function is doing?
Can you explain this whole query?

SELECT StateName, Count(StateName) AS CountOfStateName
FROM MyTable, tblStates
WHERE InStr([state],[StateName])>0
GROUP BY StateName;

 

[lespaul]

InStr is a function that returns the position of a substring within a larger string. For instance:

InStr('a', 'abcde') will return 1 (since that's the position of the 'a' in the larger string)

InStr('Florida', 'Georgia, Texas, Florida') will return something like 17 since that's where it starts.

Most of the time you really don't need to know the exact position, just that it's greater than 0 (which is what is returned if it's not found; it may even be -1, still too early!).

So, since you have a table that lists all the states when you join in as Duane has done, you can check for the presence of each state within the string and count each state.

So the query select each statename from the list and then counts each state name where it's found in the larger string.

It's a very elegant solution. Have a star!

les

Duane - As long as I don't have to go there, I don't care who's there!

 

[dhookom]

To get my solution to work with Texas and South Texas, you would need to use an expression that gets rid of the spaces following commas and adds commas to each side of the fields:

InStr("," & Replace([state],", ",",") & ",", "," & [StateName] & ",")>0

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[oaklandar]

Thanks for the additional explanation!

 

[PHV]

InStr('a', 'abcde') will return 1

Should be read :InStr('abcde', 'a') will return 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

always get that backwards!!

Most of my code is in Delphi and the equivalent function is:

Pos(substring, string)

Concept's the same though!

les