Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Peter, Jul 28, 2006.

  1. Peter

    Peter
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Record single day paddy arrival in Punjab (New Kerala) Breaking News Oct 11, 2007
    Sikh News Paddy production hits a record high in Punjab (New Kerala) Breaking News Sep 28, 2007
    Sikh News Paddy production hits a record high in Punjab (ANI via Yahoo! India News) Breaking News Sep 27, 2007
    I Will Miss You... We Had A Good Go... So Long Gallbladder! Blogs Jan 24, 2016
    Rolling the addiction ball Blogs Oct 17, 2015

  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.

    "Peter" <Peter@discussions.microsoft.com> wrote in message
    news:536BB3B0-4DB1-44FE-885E-F1AD6AB6E860@microsoft.com...
    > 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.
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    ChrisM wrote:
    > 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.

    --
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1153476563.296543.143380@i3g2000cwc.googlegroups.com...
    >
    > ChrisM wrote:
    >> 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.
     
  6. qhu@downstate.edu

    qhu@downstate.edu
    Expand Collapse
    Guest

    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

    ChrisM wrote:
    > "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    > news:1153476563.296543.143380@i3g2000cwc.googlegroups.com...
    > >
    > > ChrisM wrote:
    > >> 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.
     
  7. qhu@downstate.edu

    qhu@downstate.edu
    Expand Collapse
    Guest

    Jamie Collins wrote:
    > ChrisM wrote:
    > > 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
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 25 Jul 2006 11:59:21 -0700, qhu@downstate.edu wrote:

    >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]
     
  9. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  10. qhu@downstate.edu

    qhu@downstate.edu
    Expand Collapse
    Guest

    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

    John Vinson wrote:
    > On 25 Jul 2006 11:59:21 -0700, qhu@downstate.edu wrote:
    >
    > >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]
     
  11. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    <qhu@downstate.edu> wrote

    > 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
     
  12. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    <qhu@downstate.edu> wrote

    > 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
     
  13. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Larry Linson wrote:
    > 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.

    --
     
  14. qhu@downstate.edu

    qhu@downstate.edu
    Expand Collapse
    Guest

    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

    Larry Linson wrote:
    > <qhu@downstate.edu> wrote
    >
    > > 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
     

Share This Page