numbered rows

S

Sharp

Hi

Im using Access97.
I have imported a table from a text file.
There are many rows.
I would like to see the rows numbered.
How do you do that?

Cheers
Michael
 
S

Sharp

Hi, Michael.


An AutoNumber field will number the records. You could have allowed Access
to assign a new primary key field (ID) to each record, which would have
created an AutoNumber field that enumerates in ordinal numbers from one to
the last record.

Now that the table has already been created, you may do this manually. Open
the table in Design View and add a new field to the table by right-clicking
on the first field name and selecting the "Insert Rows" item. Name the new
field, then select "AutoNumber" from the "Data Type" combo box, then save
the table. Open the table in Datasheet View and all of the records are now
numbered on the left side.

HTH.

Gunny
Click to expand...

I followed your instructions.
RESULT: first row is numbered '500', and the following rows do not
increments evenly.
Any ideas?

Regards
Michael
 
6

'69 Camaro

Hi, Michael.

The numbers should start at 1 and be numbered consecutively, unless the "New
Values" option was changed from the default "Increment" to "Random," and
your computer doesn't have the latest service packs (Access 97 SR-2b, Jet
3.51 SP-3, MDAC 2.8 SP-1).

Delete the new AutoNumber field that you created and save the table. Save
all work, then compact the database. Open the table in Design View again
and repeat the steps I suggested before. Do you get the same result as last
time, or do you get increments from 1 to the last numbered record?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
S

Sharp

Hi, Michael.

The numbers should start at 1 and be numbered consecutively, unless the "New
Values" option was changed from the default "Increment" to "Random," and
your computer doesn't have the latest service packs (Access 97 SR-2b, Jet
3.51 SP-3, MDAC 2.8 SP-1).

Delete the new AutoNumber field that you created and save the table. Save
all work, then compact the database. Open the table in Design View again
and repeat the steps I suggested before. Do you get the same result as last
time, or do you get increments from 1 to the last numbered record?

HTH.

Gunny
Click to expand...

Access 97 SR-2b has now been installed.
The other two files do not appear to available.

Repeating the process did not change the result.
Although I admit I skipped the 'compact database' step, as I do not know
how.
Any ideas?

Cheers
Michael
 
6

'69 Camaro

Hi, Michael.
Although I admit I skipped the 'compact database' step, as I do not know
how.
Click to expand...

To compact the database (if I remember the menu correctly for Access 97),
select the Tools menu -> Database Utilities -> Compact Database.

Here are links to the downloads you may need. Make sure that you apply them
in the order you see them for MDAC, because the earlier version contains
files that the later version doesn't, but the earlier version also contains
other files that have been superseded.

Jet 3.51 Service Pack 3

http://www.microsoft.com/downloads/...88-52ec-4a8f-8bb2-8b06d6cb668d&displaylang=en

Microsoft Data Access Components (MDAC) 2.5 Service Pack 3 (This release
includes Microsoft Jet 4.0, the Microsoft Jet OLE DB Provider or ODBC
driver, the Desktop Database ODBC Drivers, or the Visual FoxPro ODBC
Driver.)

http://www.microsoft.com/downloads/...e9-dde7-4a60-b586-5d4fd6794db6&DisplayLang=en

Microsoft Data Access Components (MDAC) 2.8 (This release does not include
Microsoft Jet 4.0, the Microsoft Jet OLE DB Provider or ODBC driver, the
Desktop Database ODBC Drivers, or the Visual FoxPro ODBC Driver.)

http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

Microsoft Data Access Components (MDAC) Security Patch MS04-003 (32-bit)

http://www.microsoft.com/downloads/...E8-C14A-47B4-BFCC-87988E062D91&displaylang=en

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
D

Douglas J. Steele

Just curious as to why you're recommending MDAC 2.8 for Access 97. For that
matter, why anything to do with MDAC?
 
S

Sharp

Hi, Michael.


To compact the database (if I remember the menu correctly for Access 97),
select the Tools menu -> Database Utilities -> Compact Database.

Here are links to the downloads you may need. Make sure that you apply them
in the order you see them for MDAC, because the earlier version contains
files that the later version doesn't, but the earlier version also contains
other files that have been superseded.

Jet 3.51 Service Pack 3

http://www.microsoft.com/downloads/details.aspx?familyid=7e82ef88-52ec-4a8f-
8bb2-8b06d6cb668d&displaylang=en

Microsoft Data Access Components (MDAC) 2.5 Service Pack 3 (This release
includes Microsoft Jet 4.0, the Microsoft Jet OLE DB Provider or ODBC
driver, the Desktop Database ODBC Drivers, or the Visual FoxPro ODBC
Driver.)

http://www.microsoft.com/downloads/details.aspx?FamilyID=c57692e9-dde7-4a60-
b586-5d4fd6794db6&DisplayLang=en

Microsoft Data Access Components (MDAC) 2.8 (This release does not include
Microsoft Jet 4.0, the Microsoft Jet OLE DB Provider or ODBC driver, the
Desktop Database ODBC Drivers, or the Visual FoxPro ODBC Driver.)

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en

Microsoft Data Access Components (MDAC) Security Patch MS04-003 (32-bit)

http://www.microsoft.com/downloads/details.aspx?FamilyId=39472EE8-C14A-47B4-
BFCC-87988E062D91&displaylang=en

HTH.

Gunny
Click to expand...

All patches have been applied now.
I have followed your process to the letter,
and included the compacting step.
RESULT:
It starts at 40, and increments evenly to 78.
Then, starts at 1 and increments evenly all the way to the end.
CONCLUSION:
There is an improvement, but still does not work as expected.
I give up.

Gunny thanks for your help.
You have been very helpful.

Cheers
Michael
 
6

'69 Camaro

Hi, Michael.
CONCLUSION:
There is an improvement, but still does not work as expected.
I give up.
Click to expand...

Don't give up just yet. Open your table in Design View. Right-click on the
title bar and select Properties from the pop-up menu. Do you see anything
in the "Order By" field? If so, delete it. Close the Properties dialog
window and save the table. Open the table in Datasheet View. Are the
numbers in the AutoNumber field now in consecutive order, starting from one?

Hopefully, this fixes the last problem. But even if it doesn't (my next
suggestion would be to uninstall Access, then reinstall Access and the
service packs), I should explain some of the things about AutoNumbered
fields. The records retrieved from a table aren't necessarily in any order
unless one specifically uses a query with a sort order. The AutoNumber you
see is consecutively numbered for now, but there's no guarantee that it will
stay that way, because records get deleted or get started and not saved as a
record in the table, so there can be gaps in the numbering. And the records
won't necessarily be saved in chronological order from when they were
created. So the bottom line is that you may not want to keep this
AutoNumber field in this table if it needs to provide some meaning to you.

Depending upon what you need this row numbering for, you have several
options. If you just need a temporary row number while working with a group
of the records, you can use the techniques outlined on the following Web
page, although they do provide database bloat, so you'll need to compact the
database after you are finished using the temporary table.

ACC2000: How to Simulate a Dynamic Counter in a Table or a Query to Count
Records

http://support.microsoft.com/?id=199679

If you need this row numbering for reports, then you could add an unbound
text box in the Details section of the report and set its control source to
=1 and set the "Running Sum" as "Over Group."

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Top