If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to select only most recent records?
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? |
#2
|
|||
|
|||
How to select only most recent records?
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" wrote in message ... 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? |
#3
|
|||
|
|||
How to select only most recent records?
On Thu, 17 Aug 2006 16:32:23 -0600, "Siegfried Heintze"
wrote: 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] |
#4
|
|||
|
|||
How to select only most recent records?
John Vinson wrote: 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 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 a · 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 I hope by 'precautions' you mean 'database constraints' ;-) Jamie. -- |
#5
|
|||
|
|||
How to select only most recent records?
but it keeps no history.
how could I write an SQL SELECT statement to print only the second most recent start and stop times for each worker? Without history you can not get the "second most recent start and stop times for each worker." |
Thread Tools | |
Display Modes | |
|
|