How To Add a Sequential Count in Access Query That Resets on Change of a Field

Question

Hello:

I need to generate sequence numbers in a query.
To use a simple example, let's say I have a table with two fields:  State, City

I want to create a query that results in the two fields plus a counter that goes from 1 to n, but resets on a change in the State.

For example:

The table is as follows:

Oregon  Eugene
Oregon  Portland
Oregon  Ashland
California Van Nuys
California Tarzana
California Newport

I want to build a query that results in the following:

Oregon  Eugene    1
Oregon  Portland   2
Oregon  Ashland   3
California Van Nuys  1
California Tarzana    2
California Newport   3

The numbers would have to be built and reset by the query.

I normally use the graphical interface to create my queries, but I can also do the SQL.

Ideas?

Thanks,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 1

This would be fairly simple to do in a report

If using a query to do this, you need to assign a 'rank order' for each group (State).

You can either use a subquery or I prefer using the Serialize function.

For more info and examples of this, see http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063

Of course, the fact that you need the rank order to restart for each state makes this harder!

Answer 2

I want to create a query that results in the two fields plus a counter that goes from 1 to n, but resets on a change in the State.

Hi Rich,

In those complicated cases I use a different tactics: just add an additional field to the table: "Sequence_number". It makes the query very simple.

More important for me is the balance between the the number of times that you have to modify the "Sequence_number"-ing versus the number of times that you have to run the complicated query. A Sequence_number change is just "one-time".

In my systematics I can give Controls/Fields certain "properties". Whenever I use a Control on a form that has the property "_sequence", then the Sequence_numbers are automatically adjusted on modification. Moreover, it is standard in any application.

When a record is added the next higher Sequence_number is used, when a record is deleted, the higher Sequence_numbers are lowered, and when some in between Sequence_number gets a new value, the sequence-list is adjusted.

Perhaps you get some new ideas.

Imb.

Answer 3

also easier to do in vba ... since where the resulting strings are going wasn't mentioned

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 4

You might like to take a look at RowNumbering.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates a number of queries for, amongst other things, sequentially numbering rows, overall or per group.  Using a JOIN of two instances of a table is the most efficient method.

---

Ken Sheridan, Stafford, England

Answer 5

Peter:

I will use VBA and just create a new table with an extra field that contains the sequence number.  I can do a compare with the previous keys to rest the count.  Piece of cake.

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com