Create a query with autonumber

[Gekko . via AccessMonster.com]

I want to create a query that the first field should be an autonumber. For example I want the name and last name from the Emp table, but I want that the first field is an autonumber that identifies the record so the results of the query should look something like

Field1 Name LastName
1 Gekko NoLast
2 Mary Jane
3 Jon Doe

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=10ec70c4526f41d9bc9df4d473689eb7
*****************************************

[Trevor Best]

Gekko . via AccessMonster.com wrote:

> I want to create a query that the first field should be an autonumber. For example I want the name and last name from the Emp table, but I want that the first field is an autonumber that identifies the record so the results of the query should look something like
>
> Field1 Name LastName
> 1 Gekko NoLast
> 2 Mary Jane
> 3 Jon Doe

http://www.trevor.easynet.co.uk/AccFAQ/queries.htm#counter

--
Pretentious? Moi?

[Marco Pagliero]

My way to do this is to put in the Table a numeric field with name
"DumNr" or so, with only "one" in it, I mean the number 1. DefaultValue
also put to 1.

If you create this field afterwards you could find this action query
useful:

update mytable set dumnr = 1

In any report where I want to number the rows I put a textfield with any
name and the property RunningSum set to 1 or 2.

1 will restart numbering again and again after every grouping.
2 will number all rows in the report without restarting.

This works in a report. I didn't find a way to do the same in a query

toi pretentious? et moi, alors?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[James Fortune]

"Gekko . via AccessMonster.com" <fo...@AccessMonster.com> wrote in message news:<10ec70c4526f41d9...@AccessMonster.com>...

If you don't have a primary key or indexed field in a table you are
trying to query don't count on Access knowing even its own record
order. If you have a primary key you can do something like:

SELECT (SELECT Count(theField) + 1 FROM tblSource AS A WHERE A.TableID
< B.TableID) As RowNumber, TableID, theField
FROM tblSource AS B;

E.g.,
tblSource
TableID theField
1 Data1
3 Data12
5 Data14
19 Data27
20 Data28
22 Data3
25 Data32
26 Data33
29 Data6

produces:

RowNumber TableID theField
1 1 Data1
2 3 Data12
3 5 Data14
4 19 Data27
5 20 Data28
6 22 Data3
7 25 Data32
8 26 Data33
9 29 Data6

This idea can be put into a public function:

Public Function LineNum(lngFldVal As Long, strSource As String, strKey
As String) As Long
Dim strCrit As String
strCrit = "[" & strKey & "] < " & CStr(lngFldVal)
LineNum = Nz(DCount(strKey, strSource, strCrit)) + 1
End Function

Then it can be called like:

SELECT LineNum([TableID],"tblSource","TableID") AS RowNumber, TableID,
theField FROM tblSource;

Or

SELECT LineNum([QID], "qryNew", "QID") As RowNumber, * FROM qryNew;

Note: One way to get a unique QID is to use the key from a source
table (qryNew: SELECT tblSource.TableID AS QID...). I also tried an
old query I had lying around as qryNew :-):

qryNew:
SELECT qryOrdinalIPs.IP AS IP1, qryOrdinalIPs.ID
FROM tblIPs INNER JOIN qryOrdinalIPs ON tblIPs.ID = qryOrdinalIPs.ID
WHERE (((qryOrdinalIPs.theOrdinal)=1))
GROUP BY qryOrdinalIPs.IP, qryOrdinalIPs.ID;

SELECT LineNum([ID], "qryNew", "ID") As RowNumber, * FROM qryNew;

produced:

RowNumber IP1 ID
1 2.2.2.2 1
2 7.7.7.7 2

so getting a unique ID in a query is not usually too difficult,
thereby allowing this function to be used. Since the underlying PK is
uniquely indexed there are no tie values to consider. You can still
go back to writing a subquery if the function call slows the query too
much. The function is nice because you can usually add the row
numbers to an existing query without having to spend time determining
the SQL for the subquery.

James A. Fortune
Temporary email: jimfortune AT compumarc DOT com