A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to select only most recent records?



 
 
Thread Tools Display Modes
  #1  
Old August 17th, 2006, 11:32 PM posted to microsoft.public.access.tablesdbdesign
Siegfried Heintze
external usenet poster
 
Posts: 9
Default 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  
Old August 17th, 2006, 11:56 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old August 18th, 2006, 12:22 AM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default 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  
Old August 18th, 2006, 01:26 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old August 18th, 2006, 04:38 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:36 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.