Office Dev Center - Office Developer Documentation
Office Add-ins

Make your solution a native part of Office on every platform.

Choose a product below to learn more about add-ins.

Microsoft Graph

Connect to Office 365 data using the Microsoft Graph.

Get access to Users, Groups, Mail, Calendars, Contacts, Files, Tasks, People, Notes and more — all from a single endpoint.

See all Microsoft Graph documentation

Or, see the individual Office 365 REST API endpoints for OneDrive, Outlook and more

Office 365 Connectors

Use Office 365 Connectors to get useful information and content into your Office 365 Group.

See Office 365 Connectors documentation

 none
How do I convert rows to columns in MS Access ?using Ms-access query(select statment

    السؤال

  • Hello all, please I want help with the following issue:
    I have the following table named "CHECKINOUT" in ms ACCESS

     
             USERID      NAME        CHICKTIME
              1          K1        5:45:11 AM
              1          K1        3:45:12 AM
              1          K1        6:45:11 AM
              1          K1        3:35:13 AM
              1          K1        6:35:14 AM
              1          K1        3:35:15 AM
              1          K1        6:25:16 AM 
              2          K2        3:25:17 AM
              2          K2        5:25:17 AM
              2          K2        3:15:18 AM
              2          K2        3:25:19 AM
              2          K2        4:45:10 AM
              2          K2        2:45:11 AM
              3          K3        7:25:13 AM
              3          K3        6:15:14 AM
              3          K3        5:25:15 AM
              3          K3        3:35:12 AM
              3          K3        5:55:12 AM
              4          K4        5:05:22 AM
              4          K4        4:15:32 AM
              4          K4        3:55:42 AM
              4          K4        1:35:52 AM
              4          K4        2:43:42 AM
              .           .         .
              .           .         .
              .           .         .
              .           .         .
          
    I want to convert the previous table to the get the  following result using MS-access query
     

            USERID      NAME    CHICKIN1    CHECKOUT1    CHICKIN2  CHECKOUT2     CHECKIN3  CHICKOUT 3  CHECKIN 4  CHECKOUT4 CHECKIN5
              1          K1     5:45:11 AM  3:45:12 AM  3:45:12 AM  3:35:15 AM  4:45:10 AM
              2          K2     10:45:12 AM 11:45:12 AM 1:45:12 PM  2:45:12 AM  3:45:12 PM
              3          K3     9:45:12 AM  10:45:12 AM  2:45:12 APM 1:45:12 AM 3:45:12 AM
              4          K4     8:45:12 AM  4:45:12 PM   4:45:12 AM  5:45:12 AM  5:45:12 AM
              .
     The `CHECKIN`, `CHECKOUT`, `COLUMN_NIMBER` depend on the data in `CHECKTIME` for every user.
    11/ربيع الأول/1432 10:43 م

الإجابات

  • You can use the TRANSFORM query in access. As a starting point, you could use something like this -

    TRANSFORM First(CHECKINOUT.CHICKTIME) AS FirstOfCHICKTIME
    SELECT CHECKINOUT.USERID, CHECKINOUT.[NAME]
    FROM CHECKINOUT
    GROUP BY CHECKINOUT.USERID, CHECKINOUT.[NAME]
    PIVOT CHECKINOUT.CHICKTIME;

    Try this article - http://support.microsoft.com/kb/304348

    • تم وضع علامة كإجابة بواسطة Bruce Song 19/ربيع الأول/1432 09:56 ص
    11/ربيع الأول/1432 11:55 م

جميع الردود

  • Hello all, please I want help with the following issue:
    I have the following table named "CHECKINOUT" in ms ACCESS

     
             USERID      NAME        CHICKTIME
              1          K1        5:45:11 AM
              1          K1        3:45:12 AM
              1          K1        6:45:11 AM
              1          K1        3:35:13 AM
              1          K1        6:35:14 AM
              1          K1        3:35:15 AM
              1          K1        6:25:16 AM 
              2          K2        3:25:17 AM
              2          K2        5:25:17 AM
              2          K2        3:15:18 AM
              2          K2        3:25:19 AM
              2          K2        4:45:10 AM
              2          K2        2:45:11 AM
              3          K3        7:25:13 AM
              3          K3        6:15:14 AM
              3          K3        5:25:15 AM
              3          K3        3:35:12 AM
              3          K3        5:55:12 AM
              4          K4        5:05:22 AM
              4          K4        4:15:32 AM
              4          K4        3:55:42 AM
              4          K4        1:35:52 AM
              4          K4        2:43:42 AM
              .           .         .
              .           .         .
              .           .         .
              .           .         .
          
    I want to convert the previous table to the get the  following result using MS-access query
     

            USERID      NAME    CHICKIN1    CHECKOUT1    CHICKIN2  CHECKOUT2     CHECKIN3  CHICKOUT 3  CHECKIN 4  CHECKOUT4 CHECKIN5
              1          K1     5:45:11 AM  3:45:12 AM  3:45:12 AM  3:35:15 AM  4:45:10 AM
              2          K2     10:45:12 AM 11:45:12 AM 1:45:12 PM  2:45:12 AM  3:45:12 PM
              3          K3     9:45:12 AM  10:45:12 AM  2:45:12 APM 1:45:12 AM 3:45:12 AM
              4          K4     8:45:12 AM  4:45:12 PM   4:45:12 AM  5:45:12 AM  5:45:12 AM
              .
     The `CHECKIN`, `CHECKOUT`, `COLUMN_NIMBER` depend on the data in `CHECKTIME` for every user.
    • تم الدمج بواسطة Jackie-Sun 14/ربيع الأول/1432 03:24 ص The same thread
    11/ربيع الأول/1432 10:45 م
  • You can use the TRANSFORM query in access. As a starting point, you could use something like this -

    TRANSFORM First(CHECKINOUT.CHICKTIME) AS FirstOfCHICKTIME
    SELECT CHECKINOUT.USERID, CHECKINOUT.[NAME]
    FROM CHECKINOUT
    GROUP BY CHECKINOUT.USERID, CHECKINOUT.[NAME]
    PIVOT CHECKINOUT.CHICKTIME;

    Try this article - http://support.microsoft.com/kb/304348

    • تم وضع علامة كإجابة بواسطة Bruce Song 19/ربيع الأول/1432 09:56 ص
    11/ربيع الأول/1432 11:55 م
  • With your given data there is no way to know whether a time is a check in time or a check out time.  And there is no way to know which times should be paired with each other.

    You can not rely on the order that the records are displayed to determine the order since you would of necessity be rerranging the data.


    John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County
    12/ربيع الأول/1432 03:48 م
Office Dev Center - Office Developer Documentation
Office Add-ins

Make your solution a native part of Office on every platform.

Choose a product below to learn more about add-ins.

Microsoft Graph

Connect to Office 365 data using the Microsoft Graph.

Get access to Users, Groups, Mail, Calendars, Contacts, Files, Tasks, People, Notes and more — all from a single endpoint.

See all Microsoft Graph documentation

Or, see the individual Office 365 REST API endpoints for OneDrive, Outlook and more

Office 365 Connectors

Use Office 365 Connectors to get useful information and content into your Office 365 Group.

See Office 365 Connectors documentation