Max Date

[Guest]

Hi.
I have a table tblAttend:
key [autonumber] *key
date [date/time]
emp_no [number]
in [date/time]
out [date/time]

and table tblEmpl_list
emp_no [number] *key
name [text]

and relationship: tblAttend.emp_no -- tblEmpl_list.emp_no

Table tblAttend contains only employees who clocked in/out that day, but the
full list of employees is in tblEmpl_list. I want to create a query which
will set the date in first column which will be max date in tblAttend (date
must be in all rows) and then will show e.g. Empl_no, in, out what ever...
I know how to make this query, just have problems with setting max date in
first column for all records.
Any suggestions?

Thanks for help.

 

[Ken Snell \(MVP\)]

You have three date fields in tblAttend. I assume you want to use the field
named date:

SELECT
(SELECT Max(T.[date]) FROM tblAttend AS T) AS MaxiDate,
Q.emp_no, Q.[in], Q.out
FROM tblAttend AS Q;


Also, I note that you're using date and in as the names of fields in a
table. They and many other words are reserved words in ACCESS and should not
be used for field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[Guest]

many thanks, I knew I was close

You have three date fields in tblAttend. I assume you want to use the field
named date:

SELECT
(SELECT Max(T.[date]) FROM tblAttend AS T) AS MaxiDate,
Q.emp_no, Q.[in], Q.out
FROM tblAttend AS Q;


Also, I note that you're using date and in as the names of fields in a
table. They and many other words are reserved words in ACCESS and should not
be used for field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Hi.
I have a table tblAttend:
key [autonumber] *key
date [date/time]
emp_no [number]
in [date/time]
out [date/time]

and table tblEmpl_list
emp_no [number] *key
name [text]

and relationship: tblAttend.emp_no -- tblEmpl_list.emp_no

Table tblAttend contains only employees who clocked in/out that day, but
the
full list of employees is in tblEmpl_list. I want to create a query which
will set the date in first column which will be max date in tblAttend
(date
must be in all rows) and then will show e.g. Empl_no, in, out what ever...
I know how to make this query, just have problems with setting max date in
first column for all records.
Any suggestions?

Thanks for help.