How to select only most recent records?

[Siegfried Heintze]

I have a table implementing the time sheets. Each row contains a
worker's name (text, primary key), start time and stop time
(date/time). This is updated daily. Presently I can use a simple query
to display this but it keeps no history.

How can I modify my table to maintain a one month history? Perhaps I
could add the stop time to the primary key?

If I did that,
(1) how could I write an SQL SELECT statement to print only the most
recent start and stop times for each worker?

(2) how could I write an SQL SELECT statement to print only the second most
recent start and stop times for each worker?

 

[Jeff Boyce]

Siegfried

It sounds like you are using the worker's name as a primary key. This is
risky, as you might hire more than one "John Smith". Consider, instead,
using an employee number (if you have these), or even an Access Autonumber
field as a primary key.

Next, consider keeping two tables, not one. One table holds information
about employees. Name, address, date started, date terminated, ...

The second table is a "timesheet" table (for lack of a better term). This
table contains a record ID (again, Autonumber works fine for this), a
PersonID (from the Person/Employee table), a start date/time and a stop
date/time.

With this design, you can use a query to find the Max([Stop Date/Time])
value for each EmployeeID (a Totals query, grouped by EmployeeID and showing
Max([Stop Date/Time]).

To find the "second" most recent, build a second query that finds the
Max([Stop Date/Time]) per EmployeeID WHERE that is NOT in the previous
query. In more english-like terms, find the (next) most recent StopTime
after finding the most recent StopTime.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I have a table implementing the time sheets. Each row contains a
worker's name (text, primary key), start time and stop time
(date/time). This is updated daily. Presently I can use a simple query
to display this but it keeps no history.

How can I modify my table to maintain a one month history? Perhaps I
could add the stop time to the primary key?

You need TWO TABLES: Employees, and Timesheet.

The employee's name (and other needed personal information) would
exist only in the Employees table - and that table would have no time
information. This would have the unique EmployeeID as its Primary Key.
A Primary Key should meet three criteria: it must be unique; it should
be stable; and it's nice if it's short. Peoples' names fail on *all
three* counts, and they make very bad primary keys! If you don't have
an assigned employee number, use an Autonumber (and take precautions
to handle the possibility that you might have two employees named
Janet Smith, perhaps when Janet Herndon marries and changes her name).

The second table would have an EmployeeID field as a link to the
primary key of the Employees table, and one record per work event:
EmployeeID, StartTime, StopTime. Rather than using just one record and
updating it, you would *add* a new record every day for each employee.

If I did that,
(1) how could I write an SQL SELECT statement to print only the most
recent start and stop times for each worker?

Create a query joining the two tables; sort on StartTime; and set the
query's Top Values property to 1.

(2) how could I write an SQL SELECT statement to print only the second most
recent start and stop times for each worker?

You can use a subquery to do so.

John W. Vinson[MVP]

 

[KARL DEWEY]

but it keeps no history.
Without history you can not get the "second most recent start and stop times
for each worker."