Office developer documentation, downloads, tools | Office 365, Apps for Office, Apps for SharePoint

Office Developer Documentation

Office 365 APIs

Access Office 365 data for websites or native applications running on iOS, Android, Windows, and other device platforms with the Office 365 REST APIs.

APIs

Develop for

APIs for

Try the Office 365 APIs

Office Add-ins (formerly Apps for Office)

Build an add-in that lives inside Outlook, Excel, Word, PowerPoint, or Project to extend what you can do from right in the documents, across devices and platforms — like pulling content from the web, calling mapping services, locating news, translating text, and more.

SharePoint Add-ins (formerly Apps for SharePoint)

Build SharePoint-hosted or provider-hosted add-ins to use remote web applications, surface remote data, offload business logic, access SharePoint list data, integrate with Office applications and services, make SharePoint a portal for other applications, and more.

 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 developer documentation, downloads, tools | Office 365, Apps for Office, Apps for SharePoint

Office Developer Documentation

Office 365 APIs

Access Office 365 data for websites or native applications running on iOS, Android, Windows, and other device platforms with the Office 365 REST APIs.

APIs

Develop for

APIs for

Try the Office 365 APIs

Office Add-ins (formerly Apps for Office)

Build an add-in that lives inside Outlook, Excel, Word, PowerPoint, or Project to extend what you can do from right in the documents, across devices and platforms — like pulling content from the web, calling mapping services, locating news, translating text, and more.

SharePoint Add-ins (formerly Apps for SharePoint)

Build SharePoint-hosted or provider-hosted add-ins to use remote web applications, surface remote data, offload business logic, access SharePoint list data, integrate with Office applications and services, make SharePoint a portal for other applications, and more.