How to select only most recent records?

  • Thread starter Thread starter Siegfried Heintze
  • Start date Start date
S

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?
 
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
 
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

You can use a subquery to do so.

John W. Vinson[MVP]
 
John said:
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
Click to expand...

You missed an obvious one: it should uniquely identify an entity.
Without an additional natural key, an autonumber primary key will allow
- nay, facilitate - duplicates (e.g. the same John Smith entered
multiple times) and without exposing the autonumber values, which even
autonumber advocates agree you should never do, there is no other way
of telling entities apart e.g. is the John Smith in my office the John
Smith ID=55 or the John Smith ID=99?

Other 'nice to have' attributes of an identifier are:

· verifiable in reality e.g. extension number (dial it and see who
answers), date of birth (ask to see the birth certificate), SSN (verify
it with the trusted source), fingerprints (OK, too little trust for an
employee <g>), etc;
· industry standard e.g. SSN;
· has a trusted source e.g. SSN.

Obviously, autonumber fails on all points.
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
Click to expand...

I hope by 'precautions' you mean 'database constraints' ;-)

Jamie.

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