Access Report With Multiple Subqueries

dqmq,

Sorry, I did not mean to say that the data "should" be stored with the dates in the columns.
Only that the report structure that bingie was asking for would have been easire to create.

Sorry for not clarifying.
;-)

Jeff

bingie,

Here is a screenshot of the report "Grouped by" the day.

JeffCoachman

I'm looking for more of a calendar type view, with 8 columns (EmployeeName and the seven days) and a row for each employee.

Ok so I started with a Query as the input to the Crosstab query:

SELECT EmployeeName, ShiftDate, ShiftTypeNumber, (Format(ShiftStartTime,"HH:nn") & "->" & format(ShiftEndTime,"HH:nn")) AS ShiftTime
FROM tblShifts INNER JOIN tblEmployee ON tblShifts.EmployeeNumber=tblEmployee.EmployeeNumber
WHERE ShiftValid=Yes And ShiftDate Between #1/5/2009# And #1/11/2009#;

And this of course returns all the shifts in my week with the shift time together in one field.

The crosstab query code is

TRANSFORM First(qry.ShiftTime) AS FirstOfShiftTime
SELECT qry.EmployeeName
FROM qry
GROUP BY qry.EmployeeName
PIVOT Format([ShiftDate],"Short Date");

And this works fine as well IF each employee only has one shift each day. If they have more then one shift, the cross tab query only shows the first shift. I realize that it is showing the first record as i have specified First(qry.ShiftTime).

Now, the question is - how can i aggregate all the shift times into one field (A string would suffice will crlf in between each shift)? Can the original input query do this?

Here is some data:

From my input query:
 
EmployeeName	ShiftDate	ShiftTypeNumber	ShiftTime
chris k	         01/05/2009	0	09:30->16:00
chris k	         01/05/2009	3	16:00->18:00
chris k	         01/06/2009	0	09:30->18:00
chris k	         01/07/2009	0	09:30->18:00
chris k	         01/08/2009	0	09:30->18:00
chris k	         01/08/2009	3	14:00->18:00
chris k	         01/09/2009	0	09:30->18:00
chris k	         01/10/2009	1	00:00->00:00
 
 
So you can see that he has two shifts on 1/5 and 1/8. Here is the output from the Crosstab query:
 
EmployeeName   1/5/2009	 1/6/2009	     1/7/2009	1/8/2009	    1/9/2009     1/10/2009	 
chris k	      16:00->18:00	 09:30->18:00 09:30->18:00	14:00->18:00 09:30->18:00 00:00->00:00
 
So it only shows one shift.

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Dennis...

You're up!
;-)

Good to see you have the Crosstab working.

>Now, the question is - how can i aggregate all the shift times into one field (A string would suffice will crlf in between each shift)? Can the original input query do this?

No, it needs a little help to aggregate by concatenation.  The basis of the help is to write a function, passing the EmployeeNumber and the Date as arguments.  In the function open a recordset that iterates over the records for the given employee and date, concatenating and returning the result.  

Then, I modify qry to return the concatenated field, which  would then be input to the transform.   Qry would look something like

select employee, date,  concatshifts(employee, date) from YourTable

 

Thank you dgmg for the pointers. My crosstab query not returns exactly what I want.

I created a function as you suggested to concatenate the strings. Here it is:

Public Function ConcatShifts(employeeNumber, shiftDate)
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("C:\Documents and Settings\Desktop\Scheduler Program\DB1.mdb")

Dim sql As String
sql = "SELECT ShiftStartTime, ShiftEndTime,ShiftDate,EmployeeNumber "
sql = sql & " FROM tblShifts WHERE ShiftDate = #shftdt# AND EmployeeNumber = empnnum"
sql = sql & " AND ShiftValid = Yes"
sql = sql & " ORDER BY ShiftStartTime"
sql = Replace(sql, "empnnum", employeeNumber)
sql = Replace(sql, "shftdt", shiftDate)

Set rs = db.OpenRecordset(sql)

Dim sShifts As String
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do While Not rs.EOF
        sShifts = sShifts & rs.Fields("ShiftStartTime") & "->" & rs.Fields("ShiftEndTime") & vbCrLf
        rs.MoveNext
    Loop
End If

Set db = nothing
Set rs = nothing
ConcatShifts = sShifts
End Function


And then reformatted my first query to call this function. It also now prompts for the schedule start date:

SELECT DISTINCT tblShifts.EmployeeNumber, EmployeeName, ShiftDate, concatshifts(tblShifts.EmployeeNumber,ShiftDate) AS Shifts
FROM tblShifts INNER JOIN tblEmployee ON tblShifts.EmployeeNumber=tblEmployee.EmployeeNumber
WHERE ShiftValid=Yes And ShiftDate Between StartDate And DateAdd("d",7,StartDate);

Finally the crosstab query to pivot the data gathered in the first query. Since this is the main query, it needed to pass in the paramter to the referenced query:

PARAMETERS StartDate DateTime;
TRANSFORM First(qryGetDailyShifts.Shifts) AS Shifts
SELECT qryGetDailyShifts.EmployeeName
FROM qryGetDailyShifts
GROUP BY qryGetDailyShifts.EmployeeName
PIVOT Format([ShiftDate],"Short Date");

Now it just needs some formatting and I need to call it programatically from VB - but that's all for now.

Thank you for turning me onto the Crosstable Query!