Crosstab Query Question

trythis

Registered User.
Local time
Today, 17:08
Joined
Jul 27, 2009
Messages
67
I need a query that gives me


.........date.....date.....date
Name
name
name
name

This is like a crosstab query but I want a start time to show in the cross section of the table like this

............date.........date.........date
Name...7:00 AM.....8:30 AM....7:00 AM
name...8:00 AM.....8:00 AM....7:00 AM
(the periods are just place holders to make it look like a spreadsheet)
imaging this in rows and column like a spreadsheet

How can I make my query show the information that is in a field instead of a calculation.

Thanks,
Tina
 
Can you tell some detail about table and data on which you want to make query
 
this is a table fields are

name
department
position
startdatetime
enddatetime
workcode

the startdatetime looks like this
9/9/2009 15:05

I want the name as the row headers
the date as the column headers
the time as the junction between the two

so that I can look and see that john doe starts at 15:05 on 9/9/2009 and he starts at 14:30 on 9/10/2009 does this make sense.

Thank You.
Tina
 
Paste this sql in the query and replace field and table names
Code:
TRANSFORM 
First(Format([StartDateTime],"HH:MM")) AS ValFld
SELECT YourTable.Name
FROM YourTable
GROUP BY YourTable.Name
PIVOT Format(Int([StartDateTime]),"dd-mm-yy");
 
Last edited:
Very cool this worked great.

Another Question if I want to add another field like the type of shift.
Example

the query shows
date
7:00 AM

I have a field after the time in my table that has an s or sick or v for vacation d for day e for evening and n for night I want this to show up after the time.
date
7:00 AM d
7:00 AM n

Is this possible?

Thanks,
Tina
 
Last edited:
I would have to merge the fields to make this work wouldn't I?

Since you can only have one value.

I will research merge options.

Thanks,
Tina
 
Ok I found this

=[LastName] & ", " & [FirstName]

in my case it would be

=[ShiftStart] & "," & [ShiftType]

this would show 7:00 AM D Correct? but then how do I make it the value in the crosstab?

Thanks,Tina
 
Use this sql

Code:
TRANSFORM First(Format([StartDateTime],"HH:MM") & " " & [shifttype]) AS ValFld
SELECT YourTable.Name
FROM YourTable
GROUP BY YourTable.Name
PIVOT Format(Int([StartDateTime]),"dd/mm/yy");
 

Users who are viewing this thread

Back
Top Bottom