Query to return most recently created records (1 Viewer)

[ewan97]

I need this query to return the most recently created records in a composite key field, and only the most recent ones.

there is a screenshot of the composite key field attached

I want to get the records with the highest InvoiceID, there can be multiple records with the same InvoiceID and I want to get all of them.

Using the screenshot as an example I need to query to return the last two records as they both have the Same, highest InvoiceID.

 

[CJ_London]

something like

 SELECT * 
 FROM myTable 
 WHERE InvoiceID=(SELECT Max(InvoiceID) FROM myTable AS Temp)

 

[ewan97]

ok where in the query do I put this code?

 

[CJ_London]

that is the query

 

[ewan97]

Sorry I don't quite understand how to go about using the information you've given me, I'm pretty new to this.

 

[CJ_London]

1. open a new query (on ribbon, click create then query design)- do not select any tables or queries so just close this window
2. click on the SQL view (top left on ribbon)
3. copy and paste the code into the sql window
4. change mytable (in two places) to the name of your table - if your table has spaces put square brackets round it e.g. [my table]
5. run the sql by clicking on the view button (top left on ribbon) or
6. to view in the query builder from the view dropdown, click the design icon

If you have any problems, do not say 'it doesn't work', copy and paste the code you actually have and provide the error code and description

 

[ewan97]

yes that worked, thanks for the clear explanation