numbered rows

[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

 

['69 Camaro]

Hi, Michael.

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?

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

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.)

 

[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

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

Regards
Michael

 

['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.)

 

[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

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

 

['69 Camaro]

Hi, Michael.

Although I admit I skipped the 'compact database' step, as I do not know
how.

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.)

 

[Joseph Meehan]

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

If you follow 69's instructions you should get what you want. You need
to do all of his instructions however. You are finding out why we normally
advise users not to use Autonumbers when you are going to see the numbers
and someone may expect them to be in some sort of order. The autonumber
system was designed to give you a set of unique numbers, but maybe not
consecutive.

 

[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?

 

['69 Camaro]

Hi, Doug.

Just curious as to why you're recommending MDAC 2.8 for Access 97.

It's the equivalent of a "public service announcement." As an Oracle DBA,
I'm commonly asked why data connections aren't working as expected for just
about any application you can think of. Quite often, the drivers are
outdated on the computer in question, and installation of the latest version
of MDAC easily fixes this problem.

There are now more computers with both Access 97 and a Jet 4.0 Access
application than there are computers with just Access 97 installed.
Ensuring that the latest version of MDAC is installed fixes other problems
that the questioner didn't ask about or avoids them when a Jet 4.0 *.MDB is
eventually run on the computer in the future.

Installing other necessary service packs is an opportunity to also install
the latest version of MDAC, so I give this advice even for Access 97.
Michael admits he didn't have Access 97 SR-2b installed on his computer, so
it's a prime opportunity to check what other service packs are available for
download that may help keep his computer in tip-top shape.

And even if the advice doesn't help Michael in the immediate future, it will
probably help a dozen or more other people who read this thread, have an
outdated version of MDAC on their computers, see the links to download the
files, and then install the latest version on their own computers.

This, in turn, will cut down on the number of people who ask, "How come this
doesn't work right?"

Saves headaches. Saves time. Someone ought to make public service
announcements to make this happen more often. Dontcha think?

HTH.

Gunny

P.S. You forgot to mention my lack of warning of the perils of expected
consecutiveness of AutoNumbers. ;-)

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.)

 

[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

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

 

['69 Camaro]

Hi, Michael.

CONCLUSION:
There is an improvement, but still does not work as expected.
I give up.

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.)