get most recent inserted record

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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6314-get-most-recent-inserted-record.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314

Thanks for any help,
Steve



*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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" wrote in message
news:OIZ4BJn3FHA.696@TK2MSFTNGP09.phx.gbl...
>I am trying to get the ID of a newly inserted record. After initial
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
>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
>
>

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
>
> 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
>
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
> 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

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
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

"Steve" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
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

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
SELECT TOP 1 Field1_Name[, Field2_Name, Fieldx_Name...] FROM Table_Name
ORDER BY PK_Field_Name DESC

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" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6314
>
> 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
>
>