Welcome to SPN

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

Sign Up Now!

get most recent inserted record

Discussion in 'Information Technology' started by Steve, Nov 1, 2005.

  1. Steve

    Steve
    Expand Collapse
    Guest

    I am trying to get the ID of a newly inserted record. After initial insert,
    the rows can have the same values(Except the PK) so I can't be assured that
    I'm getting the most recent record, in fact 100% I'm not.

    Is there a way that I can SELECT a single record from a table and only
    return the row that has the highest value for a given column?

    Thanks for any help,
    Steve
     
  2. Loading...


  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Do you insert Record by code or through the Form process?

    Explain the process you use to insert Record and post code if appropriate.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Steve" <sss@sss.com> wrote in message
    news:OIZ4BJn3FHA.696@TK2MSFTNGP09.phx.gbl...
    >I am trying to get the ID of a newly inserted record. After initial
    >insert,
    > the rows can have the same values(Except the PK) so I can't be assured
    > that
    > I'm getting the most recent record, in fact 100% I'm not.
    >
    > Is there a way that I can SELECT a single record from a table and only
    > return the row that has the highest value for a given column?
    >
    > Thanks for any help,
    > Steve
    >
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You need to add a DateTime field to your table with a default =Now() so the
    when the record is created it will have the date and time.

    "Steve" wrote:

    > I am trying to get the ID of a newly inserted record. After initial insert,
    > the rows can have the same values(Except the PK) so I can't be assured that
    > I'm getting the most recent record, in fact 100% I'm not.
    >
    > Is there a way that I can SELECT a single record from a table and only
    > return the row that has the highest value for a given column?
    >
    > Thanks for any help,
    > Steve
    >
    >
    >
     
  5. bruce@aristotle.net

    bruce@aristotle.net
    Expand Collapse
    Guest

    Steve wrote:
    > I am trying to get the ID of a newly inserted record. After initial insert,
    > the rows can have the same values(Except the PK) so I can't be assured that
    > I'm getting the most recent record, in fact 100% I'm not.
    >
    > Is there a way that I can SELECT a single record from a table and only
    > return the row that has the highest value for a given column?


    Yes...e.g. if your table is named MyTable and the 'given column' is
    MyID use:

    SELECT MyTable.*
    FROM MyTable INNER JOIN (SELECT Max(MyTable.MyID) AS MaxOfMyID
    FROM MyTable)
    AS MaxMyTable ON MyTable.MyID = MaxOrders.MaxOfMyID;

    HTH,
    Bruce
     
  6. bruce@aristotle.net

    bruce@aristotle.net
    Expand Collapse
    Guest

    Well, perhaps I spoke too soon...this answers your question but the
    answer to your question may not solve your initial problem, i.e., 'how
    do I get the ID of a newly inserted record'. If this is a database to
    which you have exclusive access and each successive PK is larger than
    the previous one this SQL will return the last inserted record. If you
    are in a multi-user situation it may well return the last inserted
    record, but this record may be a record that someone else inserted, not
    _your_ last inserted record. If you are trying to retrieve the ID of
    the last record that you inserted you will need to insert the record
    using VBA with an ADO or DAO recordset in order to accurately retrieve
    that ID.

    Bruce
     
  7. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    "Steve" <sss@sss.com> wrote in message
    news:OIZ4BJn3FHA.696@TK2MSFTNGP09.phx.gbl...
    > I am trying to get the ID of a newly inserted record. After initial

    insert,
    > the rows can have the same values(Except the PK) so I can't be assured

    that
    > I'm getting the most recent record, in fact 100% I'm not.
    >
    > Is there a way that I can SELECT a single record from a table and only
    > return the row that has the highest value for a given column?
    >
    > Thanks for any help,
    > Steve


    SELECT TOP 1 Field1_Name[, Field2_Name, Fieldx_Name...] FROM Table_Name
    ORDER BY PK_Field_Name DESC
     
  8. Steve

    Steve
    Expand Collapse
    Guest

    Thanks guys!
    Bruce, I am the sole user at this time. In the event that we add more
    users, I will move the app over to SqlServer and asp.net as the users will
    be out in the field.

    Thanks for your answers, everything is up and running now


    "Steve" <sss@sss.com> wrote in message
    news:OIZ4BJn3FHA.696@TK2MSFTNGP09.phx.gbl...
    > I am trying to get the ID of a newly inserted record. After initial

    insert,
    > the rows can have the same values(Except the PK) so I can't be assured

    that
    > I'm getting the most recent record, in fact 100% I'm not.
    >
    > Is there a way that I can SELECT a single record from a table and only
    > return the row that has the highest value for a given column?
    >
    > Thanks for any help,
    > Steve
    >
    >
     

Share This Page