Row Number (1 Viewer)

[tabitha]

I know there's no rownumber function native to Access, but does anyone have a good alternative? I have two tables in a union query, and I need to add a sequential Row_ID to the end result. I was hoping to avoid using a report, and just stick to a query that I can quickly export.

Any ideas?

 

[plog]

Why do you need this? And, even with the a method that does assign a row_ID, you can never guarantee that the same rows will get the same row ID every time that query is executed.

With that said, what you need to do is search for "Running Count Query" on this forum. That would be the method to employ in assigning a unique row_ID to every row of data in a query per run of a query. Again, you can never ensure that the row_IDs generated by a query will stay with the row's they are assigned between executions of the query.

 

[tabitha]

I need this because I'm uploading a template of a census to a website, and the first column is Row_ID and it wants them numbered. I don't care if John Doe is Row_ID 1 every time, because I'm running the query on many different companies (whatever the user chooses) but I want it to be

Row_ID
1
2
3
4
..

Every time it's run.

 

[The_Doc_Man]

plog is correct.

Remember this basic principle: There is no spoon... (Whoops - wrong matrix... I mean "there is no row number") It is an illusion UNLESS you stored one when you stored the data in the table.

If your tables are inherently not numbered, then when you add or delete records the virtual row numbers would change. If you were trying to identify a row in the table to go back and look at it based on the report, it is possible that the number would be different by the time you went back to look. Even for static tables, you are skating on thin ice. For instance, if you change a parameter in the query's WHERE clause, you can affect the records. If you add a field to the query and it happens to be indexed, you can affect the numbering order. If the tables include nulls and you use null-based functions, you can affect the numbering order.

If you are looking to put numbers on the output query, what do those numbers mean? (That is a serious question, I'm not being frivolous with it.)

Your question is actually a violation of one of the Old Programmer's Rules: Access can't tell you anything you didn't tell it first. So... if you wanted to see row numbers, your design should have included row numbers. If the row numbers are purely cosmetic, why do you need them?

(If the answer is "my pointy-haired boss wants them" we sympathize.)

 

[tabitha]

(If the answer is "my pointy-haired boss wants them" we sympathize.)

More like the ONE company that we use (among 20) is demanding it. Since they have nothing to do with our database, and our database was created some 15 years ago, there was no way to prevent this. One of the other companies conveniently just asked for their already existing Employee_ID which I had readily available. I think I'm going to have to resort to creating a report with a count running sum over all..

 

[tabitha]

To give some more info.. Here is what I had, and for the most part it worked:

Dim stDocName As String

'I have a check box as to whether we want the employee's dependents to be included, so if 'the box is checked, it set the [Relationship] to "Sub" which means an employee.

If Nz(Me!IncDep.Value, False) = True Then
stDocName = "Census - UPMC"
DoCmd.OpenReport stDocName, acViewPreview, , "[Relationship]='" & "Sub" & "'"
DoCmd.RunSavedImportExport "Export-Census - UMPC"
Else
stDocName = "Census - UPMC"
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.RunSavedImportExport "Export-Census - UPMC"
End If

The problem with this is that it made the saved export the same name every time, and my coworkers want the date appended to the name of the file at export; the file could only be saved as .xls and we need .xlsx; it would open the report every time, which they just then have to close again.

So I came up with this:

Function OutputUPMCWithoutDeps() As Byte

Dim mFilename As String

mFilename = "V:\Employee Census Forms\DatabaseCensus\Census UPMC Without Deps " _
& Format(Now(), "yyyy-mm-dd_h-nnA/P") & ".xlsx"

DoCmd.OutputTo acOutputQuery, "Census - UPMC", acFormatXLSX, mFilename

MsgBox "Done outputting " & mFilename, , "Done"

End Function

If Nz(Me!IncDep.Value, False) = True Then
OutputUPMCWithoutDeps
Else
OutputUPMCWithDeps
End If

Which fixed the file name issue, but now that I'm not using a report but a query, my Row_ID is gone and I need to replace it somehow? I tried DoCmd.OutputTo acOutputReport, but got a run-time error 2282..

 

[Solo712]

To give some more info.. Here is what I had, and for the most part it worked:

Dim stDocName As String

'I have a check box as to whether we want the employee's dependents to be included, so if 'the box is checked, it set the [Relationship] to "Sub" which means an employee.

If Nz(Me!IncDep.Value, False) = True Then
stDocName = "Census - UPMC"
DoCmd.OpenReport stDocName, acViewPreview, , "[Relationship]='" & "Sub" & "'"
DoCmd.RunSavedImportExport "Export-Census - UMPC"
Else
stDocName = "Census - UPMC"
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.RunSavedImportExport "Export-Census - UPMC"
End If

The problem with this is that it made the saved export the same name every time, and my coworkers want the date appended to the name of the file at export; the file could only be saved as .xls and we need .xlsx; it would open the report every time, which they just then have to close again.

So I came up with this:

Function OutputUPMCWithoutDeps() As Byte

Dim mFilename As String

mFilename = "V:\Employee Census Forms\DatabaseCensus\Census UPMC Without Deps " _
& Format(Now(), "yyyy-mm-dd_h-nnA/P") & ".xlsx"

DoCmd.OutputTo acOutputQuery, "Census - UPMC", acFormatXLSX, mFilename

MsgBox "Done outputting " & mFilename, , "Done"

End Function

If Nz(Me!IncDep.Value, False) = True Then
OutputUPMCWithoutDeps
Else
OutputUPMCWithDeps
End If

Which fixed the file name issue, but now that I'm not using a report but a query, my Row_ID is gone and I need to replace it somehow? I tried DoCmd.OutputTo acOutputReport, but got a run-time error 2282..

Tabitha,
if your spreadsheet is not too big (say not more than several hundred rows) you could create your sequential counter by inserting a column in your query that will create the numbers recursively, like:

SELECT (SELECT COUNT(*) FROM Ttable b WHERE b.ordering_column <= a.ordering_column) as mycount, ...... ordering_column FROM Ttable a 
ORDER BY a.ordering_column;

Best,
Jiri

 

[tabitha]

I just created another query that filtered on employee, then two separate reports linked to each query, then did a DoCmd.RunSavedImportExport and named them each "UPMC With Deps" and "UPMC Without Deps". It doesn't have the date, but such is life.

 

[ButtonMoon]

Row numbering is an extremely common thing to do and it's perhaps surprising that people here seem to be questioning such a requirement. Numbering is something that's supported by every major DBMS, typically using the standard SQL RANK() / ROW_NUMBER() functions. Unfortunately Access doesn't support ROW_NUMBER() and doesn't make it so easy to number rows. That doesn't mean there's anything wrong with wanting to do it!

Here's one method, assuming you want to order the row numbers based on col1:

SELECT col1, col2, col3,
(SELECT COUNT(*)
FROM Table1 WHERE col1 <= t.col1) AS RowNum
FROM Table1 AS t
ORDER BY col1;

(EDIT: Jiri already answered with a similar solution. I didn't read his post before writing this.)

 

[plog]

Table1
col1, col2, col3
33, James, 4/18/2014
19, Sally, 5/1/2014
14, James, 4/19/2014
14, Sally, 4/19/2014
33, James, 5/1/2014
11, David, 1/12/2014

SELECT col1, col2, col3,
(SELECT COUNT(*)
FROM Table1 WHERE col1 <= t.col1) AS RowNum
FROM Table1 AS t
ORDER BY col1;

Query Results:

col1, col2, col3, RowNum
11, David, 1/12/2014, 1
14, James, 4/19/2014, 3
14, Sally, 4/19/2014, 3
19, Sally, 5/1/2014, 4
33, James, 4/18/2014, 7
33, James, 5/1/2014, 7

Our issues were with her purpose for it and the possiblity of it not serving her purpose. Your SQL demonstrates the difficulty in implementing it correctly as well.

 

[jdraw]

If all you need is a number on a record, did you consider setting up an empty table (temporarily) with the same structure, plus an autonumber field.
Then append your data to that table, and export the contents as csv.
Autonumber will add a unique number as the records are added.

Same export syntax, just a different table.

Delete the temp table when you're done.

 

[Gasman]

This site provides a function, though I had problems with references when trying it out as the code is very old, but works great when those are sorted.

http://www.lebans.com/rownumber.htm

All I had to do was specify DAO recordset.

Now have it working and learnt something new again.

 

[Solo712]

Table1
col1, col2, col3
33, James, 4/18/2014
19, Sally, 5/1/2014
14, James, 4/19/2014
14, Sally, 4/19/2014
33, James, 5/1/2014
11, David, 1/12/2014

SELECT col1, col2, col3,
(SELECT COUNT(*)
FROM Table1 WHERE col1 <= t.col1) AS RowNum
FROM Table1 AS t
ORDER BY col1;

Query Results:

col1, col2, col3, RowNum
11, David, 1/12/2014, 1
14, James, 4/19/2014, 3
14, Sally, 4/19/2014, 3
19, Sally, 5/1/2014, 4
33, James, 4/18/2014, 7
33, James, 5/1/2014, 7

Our issues were with her purpose for it and the possiblity of it not serving her purpose. Your SQL demonstrates the difficulty in implementing it correctly as well.

The only thing this exercise demonstrates is that the algorithm will not work if you count on a column that has duplicate values. It will work if there is at least one column which is unique.

Best,
Jiri