Results 1 to 4 of 4
  1. #1
    andifi is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    2

    split table and join it again side by side

    hi guys,
    i have a table, the name is CHECKINOUT and the table have fields, otherwise:

    1. EID (this is a Employes ID, but this is not a primary key and indx is No)
    2. CHECKTIME (this is a TIME and the format is ddmmyyy hh.mm.ss)
    3. CHECKTYPE (this is a teks field and in that is I or O; I is In and O is Out

    and my questions is
    how to made a query from CHECKINOUT Table to be like this?

    1. EID


    2. CHECKTIMEIN (taken from CHECKTIME and CHECKTYPE with category I
    3. CHECK TIMEOUT (taken from CHECKTIME and CHEKTYPE with category O

    im sorry my english is bad

    and thanks you for the care

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    create two querys with EID, CHECKTIME, and CHECKTYPE

    qryin - all the in records
    qryout - all the out records


    copy the following SQL into a third query


    SELECT qryin.EID, qryin.CHECKTIME, qryin.CHECKTYPE, qryout.CHECKTIME, qryout.CHECKTYPE
    FROM qryin LEFT JOIN qryout ON qryin.EID= qryout.EID;



    (I've assumed all results would be on the same day as no date field is included)

  3. #3
    andifi is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    2
    still failed,
    can you help me in my file
    https://drive.google.com/file/d/0B7S...ew?usp=sharing

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Another way is to use a crosstab query

    EID - row heading
    checktype - column heading
    checktime - value

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-25-2016, 01:12 PM
  2. Showing parameter fields side by side
    By sebgrb in forum Queries
    Replies: 2
    Last Post: 03-07-2014, 02:25 AM
  3. Report side by side comparing category
    By bbmak in forum Reports
    Replies: 5
    Last Post: 01-12-2014, 12:43 PM
  4. Replies: 11
    Last Post: 07-20-2011, 11:28 AM
  5. Multiple records side by side
    By Patience in forum Reports
    Replies: 8
    Last Post: 09-01-2010, 09:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums