Crosstab Multi Values

[gazza110]

Hi,

I am trying to use crosstab queries to devise a weekly shift schedule for employees.

I have a tblCheckIns that has fields:

*employee name
*payroll number
*date
*start time
*end time

from which I have 2 queries running on this:

qryStartTime
qryFinishTime


I have got the date as column heading and the times as values using a 'First' total.

I then use a 3rd query qryShiftTimes to bring together the start and finish times and this appears to work well.

The problem I have is with staff who work a split shift in a day, eg

08:00 - 12:00 then
17:00 - 21:00

I want the employee to appear on qryShiftTimes twice showing the early shift and then again to show the late shift, however as you have to calculate the VALUE field (using either 'first' or 'last' calculation) you cannot get the query to show both times as seperate rows.

Any ideas on a work round for this?

Thanks,
Gary.

 

[dhookom]

You can use a single crosstab query that uses the First of a concatenated values using the generic concatenation function found in the FAQs of this forum.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]