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

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

Answer 1

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...

Answer 2

Here's the first one I found...

Link to UtterAccess

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

Hope it helps...

Answer 3

Found another one...

Answer 4

Thanks for the reply I will try to get this to work and let you know how i get on

Answer 5

You're welcome. If you need further help, you might consider sharing a copy of your db.

Good luck!

Answer 6

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

Answer 7

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 [e-mail].