Auto-generated sequential numbers in queries

[Matteo Brovelli]

I'm new to Microsoft Access and SQL; I would like to know if it's
possible to design a query and add a field containing a sequential
number (without creating a table).

Thanx for your help, Matteo

[Michel Walsh]

Ciao Matteo,

You can COUNT the number of time a given not-duplicated value is found in
the same recordset, being less or equal to the value of the same field in
the actual record. You can use a Sub select, or a DCount( ), or a function
based on a static (or global) variable.

Hope it may help,
Vanderghast, Access MVP.

Matteo Brovelli wrote in message <349EB0A1...@datasport.it>...

[Pansophy]

If I could add to the question here...

I want to get a sequential number based on a grouping expression in a query.
In other words I have a query that has 5 groups of data numbered 1 through
5. I want to get a sequential number that starts over for each group. Is
this also possible??

Grp #
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
3 6

Also, if you wouldn't mind writing a simple example of how to use DCount in
the manner you described before, that would be very helpful. Neither Access
help on DCount nor any of my books were very helpful in this regard.

Thanks very much...

-Michael

Michel Walsh wrote in message
<#1JVzjFE...@uppssnewspub05.moswest.msn.net>...

[Michel Walsh]

Hi,

If you have only one single and assume it is a numeric field called
MyOrderBy, then the DCount would be (inside the QBE grid):

MySequence: DCount("*", "TableName", "MyOrderBy>=" & [MyOrderBy])

If the field is a date/time type, you must use the # decorators and the
american setting (or force it). Personnaly I always use a user defined
function in this case:

MySeq : DCount("*", "TableName", "MyOrderBy >=" & _
DateForSQL( [MyOrderBy] ) )

Now, if the count imply another condition, I just AND it. As example, if I
have to count by to take in account also the Group number, the expression
will be:

MySeq: DCount("*", "table", "MyGroup=" & [MyGroup] & _
" AND MyOrderBy >= " & DateForSQL( [MyOrderBy] ) )

In your case, MeSeq will be your field [#]. It is important to see that you
need an ordering field, such as a date stamp or otherwise. You cannot relay
on the PHYSICAL position of the record. If you are using a primary key, you
are not dependant of the physical position, so that field can be used in the
comparison, >=.

Here is DateForSQL, just in case you may find it usefull:

Public Function DateForSQL( Q As Date) As String
DateForSQL = Format(Q, "\#m/d/yy h:nn:ss AM/PM\#")
End Function

(note that the argument cannot be NULL, since a date data type cannot be
null)

Hope it may help,
Vanderghast, Access MVP.

Pansophy wrote in message <67stah$8...@dfw-ixnews4.ix.netcom.com>...