Crosstab query?

[ddog171]

I have a table with the following info:

Date/shift/name
1-1-07/1/tom
1-1/07/1/Bill
1-1-07/2/frank
1-1-07/2/Ben
1-1-07/3/Scott
1-1-07/3/Terry
1-2-07/1/tom
1-2/07/1/Bill
1-2-07/2/frank
1-2-07/2/Ben
1-2-07/3/Scott
1-2-07/3/Terry
1-3-07/1/tom
1-3/07/1/Bill
1-3-07/2/frank
1-3-07/2/Ben
1-3-07/3/Scott
1-3-07/3/Terry

What I want is the date to be displayed horizontally and the the names vertically with the shift as the intersection:

1-1-07 1-2-07 1-3-07
Tom 1 1 1
Bill 1 1 1
Frank 2 2 2
Ben 2 2 2
Scott 3 3 3
Terry 3 3 3

is this possible with a crosstab query or do I have to go about doing it some other way? If a crosstab query is possible how do you go about doing it?

Any help would be greatly appreciated.

 

[The_Doc_Man]

I'm thinking a crosstab query isn't going to like this because what you really want is not a summation. It is a calendar or schedule. Crosstabs tend to get peevish when you try to do something that doesn't contain a GROUP BY or a SUM, COUNT, MAX, or MIN function. Which, if I see this right, you would not.

This is not one of the stronger suits for Access because it doesn't really want to see data in tabular form. It wants things to be linear. Offhand I don't have a really good suggestion, but you might want to search this forum for topics such as "Schedules"

 

[ddog171]

I got it working

I tried it with a crosstab query and it is working with my test data SO FAR. Lets hope it works all the way through. Here is my SQL statement

TRANSFORM First([Select Month Schedule].shift_type) AS FirstOfshift_type
SELECT [Select Month Schedule].shift_position, [Select Month Schedule].name
FROM [Select Month Schedule]
GROUP BY [Select Month Schedule].shift_position, [Select Month Schedule].name
PIVOT [Select Month Schedule].Date;

 

[Dennisk]

Hi Ddog171,
The only problem you may have is if you want the Xtab to drive a report as you have to specify the name of each date column in your report. and I assume that the number of dates will increate month by month.