ASP.NET, SQL Server, WPF, C#, VB.NET

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 
 
"Siegfried Heintze" <siegfried@heintze.com> wrote in message 
 
news:%23cU1D0kwGHA.4408@TK2MSFTNGP02.phx.gbl...

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?

On Thu, 17 Aug 2006 16:32:23 -0600, "Siegfried Heintze" 
 
<siegfried@heintze.com> wrote: 
 
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. 
 
Create a query joining the two tables; sort on StartTime; and set the 
 
query's Top Values property to 1. 
 
You can use a subquery to do so. 
 
John W. Vinson[MVP]

John Vinson wrote: 
 
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=3D55 or the John Smith ID=3D99? 
 
Other 'nice to have' attributes of an identifier are: 
 
=B7 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; 
 
=B7 industry standard e.g. SSN; 
 
=B7 has a trusted source e.g. SSN. 
 
Obviously, autonumber fails on all points. 
 
I hope by 'precautions' you mean 'database constraints' ;-) 
 
Jamie. 
 
--