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

  • Question

  • Hi 

    I have a time keeping database that runs of sql express and I'm trying to get produce reports from this in access 

    I can get the  data in the following form from a query showing last weeks records how do I go about converting rows into columns for the same date 

    What I want to see is something like this  for each employee  on each day of the week

    employee_id first_name last_name date_and_time Clockin1 Clockou1 Clockin2 clockout2
    1 User1 One 30/04/2018 08:22 08:22:00 12:15:00 12:43:00 17:17:00
    2 User2 Two 30/04/2018 08:22 08:07:00 12:03:00 12:47:00 17:16:00

    This is what I get at the moment for the full week, on a friday it will mainly be 2 clockings aday

    employee_id first_name last_name work_swipe_id date_and_time Clocking
    1 User1 One 696690 30/04/2018 08:22 08:22:00
    1 User1 One 696739 30/04/2018 12:43 12:43:00
    1 User1 One 696725 30/04/2018 12:15 12:15:00
    1 User1 One 697154 30/04/2018 17:17 17:17:00
    1 User1 One 697679 01/05/2018 12:21 12:21:00
    1 User1 One 697657 01/05/2018 08:20 08:20:00
    1 User1 One 697718 01/05/2018 13:16 13:16:00
    1 User1 One 698612 02/05/2018 08:19 08:19:00
    1 User1 One 698656 02/05/2018 12:50 12:50:00
    1 User1 One 698120 01/05/2018 17:17 17:17:00
    1 User1 One 699011 02/05/2018 17:15 17:15:00
    1 User1 One 698638 02/05/2018 12:23 12:23:00
    1 User1 One 699552 03/05/2018 08:23 08:23:00
    1 User1 One 699582 03/05/2018 12:14 12:14:00
    1 User1 One 699985 03/05/2018 17:16 17:16:00
    1 User1 One 699646 03/05/2018 13:10 13:10:00
    1 User1 One 700508 04/05/2018 08:21 08:21:00
    1 User1 One 700529 04/05/2018 12:30 12:30:00
    3 User3 Two 696721 30/04/2018 12:03 12:03:00
    3 User3 Two 696743 30/04/2018 12:47 12:47:00
    3 User3 Two 696680 30/04/2018 08:07 08:07:00
    3 User3 Two 697148 30/04/2018 17:16 17:16:00
    3 User3 Two 697641 01/05/2018 08:01 08:01:00
    3 User3 Two 697720 01/05/2018 13:26 13:26:00
    3 User3 Two 697693 01/05/2018 12:31 12:31:00
    3 User3 Two 698572 02/05/2018 07:57 07:57:00
    3 User3 Two 698116 01/05/2018 17:17 17:17:00
    3 User3 Two 698634 02/05/2018 12:10 12:10:00
    3 User3 Two 698668 02/05/2018 13:07 13:07:00
    3 User3 Two 699009 02/05/2018 17:15 17:15:00
    3 User3 Two 699573 03/05/2018 12:00 12:00:00
    3 User3 Two 699537 03/05/2018 08:04 08:04:00
    3 User3 Two 699976 03/05/2018 17:16 17:16:00
    3 User3 Two 699622 03/05/2018 12:49 12:49:00
    3 User3 Two 700495 04/05/2018 08:10 08:10:00
    3 User3 Two 700528 04/05/2018 12:30 12:30:00
    2 User2 Three 696675 30/04/2018 08:06 08:06:00
    2 User2 Three 697171 30/04/2018 17:28 17:28:00
    2 User2 Three 697717 01/05/2018 13:07 13:07:00
    2 User2 Three 697644 01/05/2018 08:02 08:02:00
    2 User2 Three 698597 02/05/2018 08:04 08:04:00
    2 User2 Three 697719 01/05/2018 13:20 13:20:00
    2 User2 Three 698683 02/05/2018 16:03 16:03:00
    2 User2 Three 699540 03/05/2018 08:06 08:06:00
    2 User2 Three 699998 03/05/2018 17:21 17:21:00
    2 User2 Three 700543 04/05/2018 12:42 12:42:00
    2 User2 Three 700447 04/05/2018 07:53 07:53:00



    Thursday, May 10, 2018 1:29 PM

All replies

  • Hi,

    You should be able to use a Crosstab query but you might have to create an intermediate staging query first to add the clockin1/clockout1/clockin2/clockout2, etc. categories.

    Let me find a link with an example for you...

    Thursday, May 10, 2018 2:32 PM
  • Here's the first one I found...

    Link to UtterAccess

    If I find any more, I'll post back.

    Hope it helps...

    Thursday, May 10, 2018 2:35 PM
  • Thanks for the reply I will try to get this to work and let you know how i get on
    Thursday, May 10, 2018 3:01 PM
  • You're welcome. If you need further help, you might consider sharing a copy of your db.

    Good luck!

    Thursday, May 10, 2018 3:04 PM
  • You can also do this quite easily in the report itself.  The report, and the first four columns within it, would be based on a query which returns a distinct set of employee and date values, calling the DateValue function to return the date from the date_and_time column.  The other four columns would be returned by a multi-column subreport in across-then-down column layout, linked to the parent report on employee_id and the date, again returned by means of the DateValue function.

    You'll find an example of this sort of report layout in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section on 'retrieving data from the database' includes a form from which two alternative reports return the same data, the former with conventional vertical grouping, the other with the sort of layout you are aiming for.

    In your case the report would be much simpler in fact, as you do not need to generate the column headings by means of another subreport, but can simply use labels in the parent report.

    Ken Sheridan, Stafford, England

    Thursday, May 10, 2018 5:27 PM
  • Hello Runningbear1966,

    Do theDBguy or Ken's suggestions work for you? If it does, I would suggest you mark helpful reply to close the thread. If not, please feel free to let us know the current state of the issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 14, 2018 6:00 AM