Converting multiple records (rows) into one record (row) using a unique identifier

[spyldbrat]

I very new to using Access and I am using Access 2007. I am trying to create a Query that shows an employee's weekly scheduled on the same row. I am importing the schedules from Excel which show each employee's work date on separate rows:

Example:



EXCEL IMPORT:

1. EE # EE Name Day of Week WorkDate Hours InTime OutTime Lunch
2. 18264 Jane Doe Monday 06/22/15 6 8:00 14:30 0.5
3. 18264 Jane Doe Tuesday 06/23/15 6 8:00 14:30 0.5
4. 18602 Jason Thomas Monday 06/22/15 8 16:00 23:59 0
5. 18602 Jason Thomas Sunday 06/21/15 8 16:00 23:59 0
6. 17529 John Smith Monday 06/22/15 4 17:00 21:00 0
7. 17529 John Smith Sunday 06/21/15 11 10:00 21:30 0.5
8. 18509 Mary Harris Monday 06/22/15 6 9:00 15:00 0
9. 18509 Mary Harris Sunday 06/21/15 6 11:00 17:00 0
10. 18509 Mary Harris Tuesday 06/23/15 6 11:00 17:00 0

How I need the Query to read:

1. EmployeeNumber EmpName Sunday Sunday Date Hours InTime OutTime Lunch Monday Monday Date Hours InTime OutTime Lunch Tuesday Tuesday Date Hours InTime OutTime Lunch
2. 18264 Jane Doe Monday 06/22/15 6 8:00 14:30 0.5 Tuesday 06/23/15 6 8:00 14:30 0.5
3. 18602 Jason Thomas Sunday 06/21/15 8 16:00 23:59 0 Monday 06/22/15 8 16:00 23:59 0
4. 17529 John Smith Sunday 06/21/15 11 10:00 21:30 0.5 Monday 06/22/15 4 17:00 21:00 0
5. 18509 Mary Harris 06/21/15 6 11:00 17:00 0 Monday 06/22/15 6 9:00 15:00 0 Tuesday 06/23/15 6 11:00 17:00 0

Sorry that the formatting is not lined up!

[CJ_London]

investigate using a crosstab query - the problem you have is that for each day you want to see three columns per day - whereas a crosstab only shows one column per day. There are two ways you can do this with the data you have.

The first is to have three crosstabs, one each from intime, outtime and lunch - then have a 4th query to combine them together

The other is to combine intime, outtime and lunch into one column as a text field e.g.

daydata=format(intime,"hh:mm") & " " & format(outtime,"hh:mm") & " " & lunch

this assumes you display time as 08:00 rather than 8:00

if you don't want the preceding 0 then instead of " ", use space(9-format(intime,"h:mm"))

[spyldbrat]

Hi Ajax. Sorry it has taken me so long to get back to you but I have been working on creating the database in the midst of other things. I created all the crosstabs and it worked like a charm!!! However, in my original post, I simplified it by excluded some things which are now causing duplicate records. I created a crosstab using two tables: One from the schedules and one for our jobs they will be scheduled to work (the system the data is being extracted from forces me to use two tables). I need all information about the job (address, city, state, zip) to show under each day of the week (on the same row) for the employee. The crosstab I created uses the employee name, employee number and job number (all grouped by row heading) from the schedule table, day of the week (column heading) from the schedules and then the job address, etc (first;value) from the jobs table. The result is showing employee's who work at more than one job on separate lines (under their respective day). I need to show all on one line. Do you know how to fix this?

[June7]

Cannot include the job number as a Row Heading field. Which means the address info will not be pulled from each job, only the first job record. Review:

http://allenbrowne.com/ser-67.html

http://allenbrowne.com/func-concat.html

[CJ_London]

Do you know how to fix this?

I think you need to find a different way of presenting your data - it may be the second option I suggested could do what you want.