Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Max Date

Discussion in 'Information Technology' started by realspido, Jul 28, 2006.

Tags:
  1. realspido

    realspido
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    General Max Arthur Macauliffe History of Sikhism Sep 4, 2013
    SciTech Mammals pick offspring’s sex to maximize number of grandchildren, study shows Breaking News Jul 11, 2013
    Events Year 2013 is centennial to honour max arthur macauliffe History of Sikhism May 8, 2013
    Sikhi To The Max developers Information Technology Mar 26, 2013
    NRI brides from region face max desertions Hard Talk Jan 22, 2010

  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    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>


    "realspido" <realspido@discussions.microsoft.com> wrote in message
    news:2E917109-6ED0-42F6-BC55-881F3845A865@microsoft.com...
    > 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.
     
  4. realspido

    realspido
    Expand Collapse
    Guest

    many thanks, I knew I was close;)

    "Ken Snell (MVP)" wrote:

    > 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>
    >
    >
    > "realspido" <realspido@discussions.microsoft.com> wrote in message
    > news:2E917109-6ED0-42F6-BC55-881F3845A865@microsoft.com...
    > > 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.

    >
    >
    >
     

Share This Page