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.

Mary M wrote:

>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

-- 
Marsh
MVP [MS Access]

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" <marshbarton@wowway.com> wrote in message 
news:7n86531vakn9h5tgobnvamlageuem0mk46@4ax.com...
> Mary M wrote:
>
>>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
>
> -- 
> Marsh
> MVP [MS Access] 

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 M wrote:
>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?
>
>
>> Mary M wrote:
>>>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.
>>
>
>"Marshall Barton" wrote
>> 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