How to query for latest record by date & time?

  • Thread starter Thread starter hkgary33
  • Start date Start date
H

hkgary33

Dear all,
I've built a table with three fields: NAME;DATE;TIME
this table will be automatically append a new record with current date and
time in the DATE & TIME field when the user input their name in the NAME
field. So, if a user input his own name in two different time period, two new
records will be appended,and the table will like:
NAME DATE TIME
Gary 2006-07-10 16:30:00
Gary 2006-07-10 17:10:00


The problem is that I want to perform a QUERY so that when the user input the
NAME field, I can search out and display the latest date and time of that
particular user....but I don't know how to use SQL statement to find out the
latest date and time fields.....can you all help me for writing such a
statement?
THANKS A LOT!!!!
 
1. For starters, Name, Date, and Time are all reserved words in JET SQL.
Rename the fields to something else so they are not ambiguous.

2. Consider combining the date and time into one field.
That will make it easier to query.

3. Having done that, create a query using this table.
3.1 In query design view, depress the Total button on the toolbar.
Access adds a Total row to the grid.
3.2 Drag your name field into the grid.
Accept Group By under this field.
3.3 Drag the date/time field into the grid.
Choose Max in the Total row under this field.

The query now shows the most recent date/time value for each name.
 
Allen said:
Name, Date, and Time are all reserved words in JET SQL.
Rename the fields to something else so they are not ambiguous.
Click to expand...

'Name' is not a reserved word in Jet 4.0:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

The table name (which would prefix the column name in the data
dictionary) could provide the data element's class name and eliminate
ambiguity e.g.

data dictionary name = Customer_LastName_Value
SQL DDL = CREATE TABLE Customers (last_name VARCHAR(35) NOT NULL, ...);

Jamie.

--
 
Back
Top