get most recent inserted record

S

Steve

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
 
K

KARL DEWEY

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

Van T. Dinh

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

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

bruce

Steve said:
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?
Click to expand...

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
 
B

bruce

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
 
R

Ron Hinds

Steve said:
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
Click to expand...

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

Steve

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
 
Top