Help displaying the 6 most recent records

[rosscortb]

Hello,



Hoping you can help please.

I have trying to display only the 6 most recent records of job title history in a form. If you see the attached, its shows the first job record twice but how do I get it show the second record and so on but capped at 6 the six most recent.

Hope that makes sense

If you need more info let me know.

Thanks
Ross

[orange]

From W3Schools SQL

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

[Micron]

If both of those records are for the same employee id there must be another table involved that's causing the additional records?

[orange]

Ross,
Can you show us the code behind the form? Or a copy of the database, so we can see the details.

[isladogs]

Create a query where you sort the required fields by the date field in descending order -newest first.
Now in the property sheet enter 6 for the Select Top property... Or in SQL view, start with SELECT TOP 6 ...

[Micron]

In both records, both start and both end dates are the same - so both records will be in the top 6 group, yes? Yet they may be duplicates.
Without seeing the employee id, can't tell if one should be removed or not.

[isladogs]

Sorry forgot about the duplicates part of the question.
Try starting your query with SELECT DISTINCT TOP 6 to get unique values

To prevent duplicates in your table, use a composite index on appropriate fields.