Add sequential numbers in query

M

Mary M

Is there a way to automatically add sequential numbers to a query where
there are multiple records for each primary key?

For example:

Table Fields
Cust# Name Phone# #in seq
Records: 1 test 5551212 1
1 test2 5552121 2
2 test3 5552222 1

Basically, I would like to automatically populate the #inseq field based on
the # of records per cust.

Many thatanks in advance.
 
M

Marshall Barton

Mary said:
Is there a way to automatically add sequential numbers to a query where
there are multiple records for each primary key?

For example:

Table Fields
Cust# Name Phone# #in seq
Records: 1 test 5551212 1
1 test2 5552121 2
2 test3 5552222 1

Basically, I would like to automatically populate the #inseq field based on
the # of records per cust.


To do that, you must have a field that can be used to sort
the records in a unique way. From your sample data, I don't
see such a field (or set of fields).

If you do have a field that determines a unique sort, then
use a subquery:

SELECT table.*,
(SELECT Count(*)
FROM table As X
WHERE X.sortfield <= table.sortfield
And X.[Cust#] = table.[Cust#]) As Seq
FROM table
 
M

Mary M

Thanks for the reply. The customer # is unique per customer. Also, currently
the #in seq is blank, I was hoping to populate with essentially the count
number in the series of each customer #

This is a one time update. Will your query work based on the additional info
provided?




Marshall Barton said:
Mary said:
Is there a way to automatically add sequential numbers to a query where
there are multiple records for each primary key?

For example:

Table Fields
Cust# Name Phone# #in seq
Records: 1 test 5551212 1
1 test2 5552121 2
2 test3 5552222 1

Basically, I would like to automatically populate the #inseq field based
on
the # of records per cust.


To do that, you must have a field that can be used to sort
the records in a unique way. From your sample data, I don't
see such a field (or set of fields).

If you do have a field that determines a unique sort, then
use a subquery:

SELECT table.*,
(SELECT Count(*)
FROM table As X
WHERE X.sortfield <= table.sortfield
And X.[Cust#] = table.[Cust#]) As Seq
FROM table
 
M

Marshall Barton

No. As I said, a unique sorting must be specified within
each Cust# group.

There is no such thing as a "count number" in a table, it
must be calculated in a query using a unique ordering of the
records. IOW, because records are stored in a table in an
arbitrary position, there is no way to reliably determine
anything about each record that is not in it's data fields.

If all existing records have a Seq# value, then you can use
a form's BeforeUpdate event with the DMax function to get
the next number, but your question is more about getting to
this point instead of how to maintain the sequence number
after the table has correct values in the field.
--
Marsh
MVP [MS Access]


Mary said:
Thanks for the reply. The customer # is unique per customer. Also, currently
the #in seq is blank, I was hoping to populate with essentially the count
number in the series of each customer #

This is a one time update. Will your query work based on the additional info
provided?


To do that, you must have a field that can be used to sort
the records in a unique way. From your sample data, I don't
see such a field (or set of fields).

If you do have a field that determines a unique sort, then
use a subquery:

SELECT table.*,
(SELECT Count(*)
FROM table As X
WHERE X.sortfield <= table.sortfield
And X.[Cust#] = table.[Cust#]) As Seq
FROM table
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top