"Gekko . via AccessMonster.com" <fo***@AccessMonster.com> wrote in message news:<10******************************@AccessMonst er.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/Abu...9df4d473689eb7
*****************************************
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