Insert new column

Q

qing

Hi,

For example;
In a table i have 50 data. I need to insert a new column so that to keep
track the number of data I have. In that column, how do I key in the number
starting from 1 and up to the end of the data, which is 50. I can't possibly
manually key in those fields in the column up to 50 as time is consuming. Is
there any ways?

Thanks.
 
K

Klatuu

An Autonumber field will not correctly tell you how many records you have in
a table. Even adding a column and incrementing it by one for each new record
will not be reliable. If you delete a record, now your record counting
column is wrong.

A recordset has a RecordCount property. You can always determine the number
of records in a recordset by using:

Set rst = Currentdb.OpenRecordset("MyTable")
With rst
If .RecordCount > 0 Then
.MoveLast
End If
MsgBox "There Are " & .RecordCount & " Records in MyTable"
.Close
End With
Set rst = Nothing
 
D

Douglas J. Steele

Alternatively,

MsgBox "There are " & DCount("*", "MyTable") & " records in MyTable"
 
J

John W. Vinson

Hi,

For example;
In a table i have 50 data. I need to insert a new column so that to keep
track the number of data I have. In that column, how do I key in the number
starting from 1 and up to the end of the data, which is 50. I can't possibly
manually key in those fields in the column up to 50 as time is consuming. Is
there any ways?

Thanks.
Click to expand...

STOP.

I'm almost certain that your table design *is wrong*. If you have fifty fields
for the same kind of data, you are "committing spreadsheet"; you appear to
actually have a one to many relationship which needs two tables, one with (up
to) fifty ROWS, not fifty fields.

Please describe your table and the nature of the data that it contains.

Whatever the table structure, it is neither necessary nor appropriate to have
a table field for a count of values; that should be calculated as needed using
a Query.
 
J

John W. Vinson

Alternatively,

MsgBox "There are " & DCount("*", "MyTable") & " records in MyTable"
Click to expand...

I think Qing is committing spreadsheet: as I read it, he or she has fifty
*FIELDS* and wants to count how many of them have a value (nonzero? non-null?
don't know).
 
Q

qing

Hi,

Sorry that I might have misunderstood you guys. What I meant was rows. Let
say I've 50 rows and I need to insert a new column to start keying in the
number starting from 1 up to the end of my rows which is 50, in the column.
For example; in the first row: i need to key in 1, second row: 2, third row:
3 and all the the way to the last row whereby it would be 50. I cant possibly
type in manually starting from 1 to 50.
 
J

John W. Vinson

Hi,

Sorry that I might have misunderstood you guys. What I meant was rows. Let
say I've 50 rows and I need to insert a new column to start keying in the
number starting from 1 up to the end of my rows which is 50, in the column.
For example; in the first row: i need to key in 1, second row: 2, third row:
3 and all the the way to the last row whereby it would be 50. I cant possibly
type in manually starting from 1 to 50.
Click to expand...

What are you trying to ACCOMPLISH? There is most likely an easier way!

For what it's worth, I'll usually have a table named NUM with one Long Integer
field N, with values from 0 through 10000 or so. This is useful in all sorts
of contexts. Now I just import a Num table from an older database, but you can
also create it by opening Excel, putting 0 in cell A1, selecting cells A1
through A10000 and choosing Edit... Fill... Series. Then copy and paste into
your Access table.
 
Top