How can add a record within other records (not last one) in Access

[Guest]

How can I add a new record within all other records? I can only add a new
record at the end (last). e.g. I have 10 records, and want to add a new
record between record 5 and 6, not the last (record 11). Thanks.

 

[ChrisM]

Hi Peter,

The simple answer is that you can't.

In a relational database, the order of the records in the table should not
be significant.
A new record is always added at end of the table.
If you want to display records in a different order, you should write
queries that will manipulate the data in whatever way you require.

Cheers,
Chris.

 

[Jamie Collins]

In a relational database, the order of the records in the table
should not be significant.

I know what you mean: a table in a SQL database has no inherent order
and 'start, 'end', 'next' and 'previous' have no meaning. If you want
those concepts, you can create a cursor (not to be confused here with a
caret) in the guise of a Recordset and invoke its Sort, MoveFirst and
MoveNext methods and test its EOF ('end of file') and BOF properties.

Therefore I think you confuse the issue when you go on to say, "A new
record is always added at [the] end of the table." It should be
sufficient to say that when a row is added to a table its physical
location has no significance.

Jamie.

--

 

[ChrisM]

In a relational database, the order of the records in the table
should not be significant.

I know what you mean: a table in a SQL database has no inherent order
and 'start, 'end', 'next' and 'previous' have no meaning. If you want
those concepts, you can create a cursor (not to be confused here with a
caret) in the guise of a Recordset and invoke its Sort, MoveFirst and
MoveNext methods and test its EOF ('end of file') and BOF properties.

Therefore I think you confuse the issue when you go on to say, "A new
record is always added at [the] end of the table." It should be
sufficient to say that when a row is added to a table its physical
location has no significance.

Jamie.

--

Jamie,
Yea, I agree with what you are saying, and yes, I suppose it IS slightly
misleading to say a new record can only be added to the end of the table,
when I've just said that the order is insignificant.
HOWEVER, in the context of the OPs question, I was trying to answer in as
simple a way as possible that there was no way of adding records into the
'middle' of a table, and nor should he be wanting or needing or even trying
to, as doing such a thing has no meaning.

Cheers,

Chris.

 

[qhu]

Thank you for your answers.
I am sorry to hear that I cannot add a new record within other records.
Microsoft Excel and Works can create a new row (record).
I create a Library database, and need to add records within records as
the library' books are filed by subject. If I always add new records at
the end, it won't work for the Library' situation. I have tried to
setup "call # field" as "key field", but it won't allow me to add a new
record within existing records.
Microsoft Access should consider this, and follow Excel or Works method
in the future.
Again, thank you all.
Peter

In a relational database, the order of the records in the table
should not be significant.

I know what you mean: a table in a SQL database has no inherent order
and 'start, 'end', 'next' and 'previous' have no meaning. If you want
those concepts, you can create a cursor (not to be confused here with a
caret) in the guise of a Recordset and invoke its Sort, MoveFirst and
MoveNext methods and test its EOF ('end of file') and BOF properties.

Therefore I think you confuse the issue when you go on to say, "A new
record is always added at [the] end of the table." It should be
sufficient to say that when a row is added to a table its physical
location has no significance.

Jamie.

--

Jamie,
Yea, I agree with what you are saying, and yes, I suppose it IS slightly
misleading to say a new record can only be added to the end of the table,
when I've just said that the order is insignificant.
HOWEVER, in the context of the OPs question, I was trying to answer in as
simple a way as possible that there was no way of adding records into the
'middle' of a table, and nor should he be wanting or needing or even trying
to, as doing such a thing has no meaning.

Cheers,

Chris.

 

[qhu]

In a relational database, the order of the records in the table
should not be significant.

I know what you mean: a table in a SQL database has no inherent order
and 'start, 'end', 'next' and 'previous' have no meaning. If you want
those concepts, you can create a cursor (not to be confused here with a
caret) in the guise of a Recordset and invoke its Sort, MoveFirst and
MoveNext methods and test its EOF ('end of file') and BOF properties.

Therefore I think you confuse the issue when you go on to say, "A new
record is always added at [the] end of the table." It should be
sufficient to say that when a row is added to a table its physical
location has no significance.

Jamie.

For the table, physical location has significance in the Library
database. For example, under Library of Congress classification, most
computer books are under QA76.76, and medical books are under R... With
current Access, all records are mess up unless you search by "queries".
That is not as convenient as Excel or Works.
Peter

 

[Ron2006]

When you are looking at tables with queries you simply add a sort. If
you do that you can put in 20 records inbetween record 5 and 6 or
between any other records.

I don't see what your real problem is.

Simply sort the records.

Ron

 

[John Vinson]

With
current Access, all records are mess up unless you search by "queries".
That is not as convenient as Excel or Works.

Excel is a spreadsheet.

Access is a relational database.

THEY ARE DIFFERENT. Assuming that Access is "Excel on Steroids" or
"grown-up Works" will simply get you into confusion and difficulties.

Queries are *NOT* frills or add-ons; they are as essential to the use
of Access as cell references and calculations in cells are to Excel.
Learn to use them; you'll be impressed.

John W. Vinson[MVP]

 

[qhu]

Thanks for your reply. At least I learned that I could not add a new
record within other records.
To my knowledge, Microsoft Works is more flexible. You can add a new
record anywhere. I believe it is database software too.
In the Library, the shelflist is filed by call number (subject), not by
the date I enter the records. Though user can sort them later by
"queries" or other methods, it is not convenient for them to browse
the list only.
Again, thank all who relied my question. \
Peter

 

[Larry Linson]

Thank you for your answers.
I am sorry to hear that I cannot add a new record within other records.
Microsoft Excel and Works can create a new row (record).
I create a Library database, and need to add records within records as
the library' books are filed by subject. If I always add new records at
the end, it won't work for the Library' situation. I have tried to
setup "call # field" as "key field", but it won't allow me to add a new
record within existing records.
Microsoft Access should consider this, and follow Excel or Works method
in the future.

If you expect one software product to be identical to another, you are going
to live your life in sorrow, because it is never the case.

Excel and Works don't even _claim_ to be relational databases, but in
relational databases, BY DEFINITION, records in tables are _unordered_, but
queries provide you the means to retrieve and work with the records in
whatever order you need.

Experienced database users and developers are unhappy with every deviation
from relational databae rules that is introduced into Access, and you are
proposing a very basic change.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

Thanks for your reply. At least I learned
that I could not add a new record within
other records.

There is no "within other records" concept in relational databases.

To my knowledge, Microsoft Works is
more flexible.

If Works suits your desires better, then, by all means, use it, instead. It
lacks a great many features that Access has, and you will regret doing so in
the future, but because you learned on it and Excel, you may like it better
now.

You can add a new record anywhere. I
believe it is database software too.

Neither Excel nor Works is _relational_ database software.

In the Library, the shelflist is filed by
call number (subject), not by the date
I enter the records.

Records in Tables in Access are not _stored_ in _any_ order; they are
displayed in the order most convenient for the implementers of Access itself
if viewed directly. That is often, but not always in primary key order, and
is certainly not even often in order of the date that the records are
entered. First thing you can try is to click on the call number field, and
set it as the Primary Key. But, see below...

Though user can sort them later by
"queries" or other methods, it is not
convenient for them to browse
the list only.

If you are creating an application, and allow the users direct access to
Tables, you are going to have far more worries than the order in which
Records are retrieved. There are far too many things that users can do,
inadvertently without realizing what they are doing, in datasheet view
(direct table view) that are problematical. If you do that, you'll be
sorry.

Larry Linson
Microsoft Access MVP

 

[Jamie Collins]

Records in Tables in Access are not _stored_ in _any_ order

Microsoft has not revealed every detail of the mdb format. However, as
I know you know and to which you have alluded here, MSFT have revealed
the order in which rows/records of data are stored in the physical
implementation.

I understand that you are trying to urge the OP explicitly specify the
row/record order they require but I think you are confusing things e.g.
by discussing physical ordering on disk [OT, did you ever get around to
testing whether a unique constraint comprising nullable and
non-nullable columns would be used for clustering in the absence of a
PK?]

Another aspect that I think is confusing is demonstrated by these
quotes (my emphases):

[Tables] are
displayed in the order most convenient for the implementers of Access itself
if viewed *directly*.

If you ... allow the users *direct* access to
Tables

The implication is that opening up the 'table dataview' or whatever it
is called in the Access UI is somehow the table itself or at least a
'higher plain' or 'divine access' to the table. Your hex editor may be
considered more *direct* access to the data than the Access UI.

I think the sooner the OP learns that Access's 'table dataview' is just
another recordset shown in a grid control on a form in a third party
(i.e. not Jet) application the better.

Neither Excel nor Works is _relational_ database software.

Arguably, an Access is not relational database software either. So
let's not argue but instead use the less contentious term, 'SQL
database'.

Jamie.

--

 

[qhu]

Once I tried to setup "call number" field as "primary key", but the
screen says, "Index or primary key cannot contain a null value". I
don't know how to change it. I setup call number field as "text". If
you are familiar with the Library of Congress system, you may know that
call number includes both letter(s) and number, such as "QA76.76 .L8
N39 2005" Can I setup this kind of number as primary key? How?
BTW, have you heard of Aleph 500 database which many colleges are using
it for online catalog. Have you ever compare it to Access? Are they
similar?
Thanks.
Peter