Working with ranges - counting subgroups and assigning numbers 1

[tvsmvp]

I think I've been trying to reinvent the wheel for about two days now.

Using a query (as opposed to a macro I'd have to run to update my table) I need to count how many entries in my db live in a particular state - and break these entries down into groups of, let's say 15, placing a corresponding number into a field. So, if "Springfield, IL" was the 14th occurrence (of "IL"), "Chicago, IL" was the fifteenth and "Indianapolis, IN" immediately followed those two, the third field I'd like to create would list out: IL-14, IL-15, IN-1. (Note the third starts back at "1")

Is there a function that I can use for this? I've spent the better part of two days trying to fashion something with dcount - and the best I've come up with is: Placement = DCount("[State]", "Pros", "[State] = '" & State & "'And" & "[Pros].[ID] < " & ID). This works by counting all occurrences of a particular state which have an ID lower than the present entry. The problem is, however, that because it's based on the ID order the entries get out of alphabetical order the moment I add an entry with a higher auto ID number. I'm going to try placing the table in alpha order, then manually adding a field of numbers - but this seems pretty barbaric.

Any thoughts?

 

[dhookom]

Why do you need to store any value in your field when this can be calculated? Have you tried an expression like:

Placement = DCount("[State]", "Pros", "[State] = '" & State & "'And" & "[City] < """ & [City] & """").

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]

 

[tvsmvp]

Thanks for the help!

I need to "store the calculation" only long enough to figure it and then export - otherwise, I wouldn't be storing, you are right about that.

I'd begun thinking about using one of the other fields (like "city," as you suggest) but was having no luck until I got your post. I'm thinking I was tripping up on the apostrophes/quote marks due to the text string/number match ups (or mix ups).

With your aid, I got it to work, after making another tweak or two: Placement = DCount("[State]", "Pros", "[State] = '" & myField & "'And" & "[City] < """ & myField2 & """")
I am/have been using this in a UDF. The "myField2" was swapped in when access kept coming back with the number "1" as the only answer. Turns out it wasn't recognizing that final use of the word "city." "City," of course, is a field name, but being used there as a variable (or supplied as such).

Thanks again

 

[dhookom]

You can get rid of some stuff in your expression like:

Placement = DCount("[State]", "Pros", "[State] = '" & myField & "' And [City] < """ & myField2 & """")


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]

 

[tvsmvp]

You're right!

Thanks again -