Return the LAST records for EACH of a particular field?

[jnmunsey]

I need to do the following:

I have a table named QuoteLog. It has a "QuoteID" field(duplicates allowed) and a "QuoteComments" field.

Each QuoteID can have multiple QuoteComments, so you could have QuoteID 1234 exist 10 times with comments for each..

I want to return the LAST record for each QuoteID in a SQL statement (for Access 97)...

So if there were 10 QuoteIDs with 10 QuoteComments each then there would be 100 records total - I want the last(the one most recently entered/modified) for EACH of the QuoteIDs - meaning I'd get 10 records.

If this is not doable, then a date entered field would suffice and returning the most recent one for EACH ID based on the date entered would be fine.

Please help!

Thanks

John M

 

[RV]

John,

>It has a "QuoteID" field(duplicates allowed) and a "QuoteComments" field.

Each QuoteID can have multiple QuoteComments<

So there's a 1:n relation between Quotes and Comments...
It's therefore advised to use a separate table for your Comments....

>I want to return the LAST record for each QuoteID in a SQL statement (for Access 97)...<

That should be easy when your database was more normalized (create a separate table for Comments and join this table to your "original" table).
Why? Cause now you don't know which record is the last record ....

You can:

1) redesign your database structure as advised or....
2) add a column, datatype Date, to your current table and build your query around the most recent Date per QuoteID (bad advise!!!!).

HTH,

RV

 

[jnmunsey]

Well the table currently only has two fields - QuoteID and QuoteComments, so how would a separate table be of any benefit? It already is in its own table and is linked to the main table.

Does there not have be a field(QuoteID) to identify which QuoteID in the main table the comments belong to..?

I know there is a LAST function but it seems to only return one record in a table, not multiple which is needed here.

In any case, what would the syntax be for diplaying the latest comments record for each ID whether or not they are organized by date?

Thanks for the input,

John M

 

[RV]

John,

>I know there is a LAST function but it seems to only return one record in a table, not multiple which is needed here.<

You can't use the LAST function here.
If you want to retrieve to last entered records you'll either have an unique automatic sequence number or a Date field in your table.
Currently, you have neither.

You're suggesting your table is linked to another table through a 1:n relationship.
If so, that's not quite (not at all ) what you indicated in your original post...

>In any case, what would the syntax be for diplaying the latest comments record for each ID whether or not they are organized by date?<

If you do not have a DateEntered or a CommentsNumber created by an unique autosequence, you won't be able to achieve what you're asking... (no such things as Record ID's in Access...).

RV

 

[Jon K]

Without a date entered field, it is not doable.

Assuming you have added the field, the query to use is:-

SELECT *
FROM QuoteLog
WHERE QuoteID & DateEntered in (SELECT QuoteID & Max(DateEntered) FROM QuoteLog GROUP BY QuoteID);

 

[jnmunsey]

Thanks I think that did it..

That seems to work, though I need to include the time or it will return all records that have the same date...

Thanks again!

 

[Jon K]

In the Design View of the table, type Now() in the Default Value cell of the DateEntered field.

This will automatically register the date and time a record is created. The Max() function in the query will select the record for each QuoteID with the latest date/time.