Creating A Schedule Using CrossTab Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a crosstab query that will allow me to display in military
time the hours that someone is scheduled to work on a particular day. Is
there a way not to calculate a field in the total area of a crosstab so I can
do this? Here is a sample of what I would like to do.


2/1/2005 2/2/2005 2/3/2005
Jane Doe 07:00-17:00 07:00-17:00
John Doe 15:00-23:00 15:00-23:00

Thank you!
 
Shannon said:
I need to create a crosstab query that will allow me to display in military
time the hours that someone is scheduled to work on a particular day. Is
there a way not to calculate a field in the total area of a crosstab so I can
do this? Here is a sample of what I would like to do.


2/1/2005 2/2/2005 2/3/2005
Jane Doe 07:00-17:00 07:00-17:00
John Doe 15:00-23:00 15:00-23:00
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this (not fully tested)? The following query assumes you have
a table like this:

CREATE TABLE table_name (
person_name VARCHAR(30) ,
date_column DATETIME , -- the column dates
time_from DATETIME , -- the start times in the person/dates grid
time_to DATETIME -- the end times in the person/dates grid
)

The Format(...,"Short Time") will give 24-hr time.

TRANSFORM Format(First(time_from),"Short Time") & "-" &
Format(First(time_to),"Short Time") As DaValue
SELECT person_name
FROM table_name
WHERE < criteria >
GROUP BY person_name
PIVOT date_column

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijdB4echKqOuFEgEQI1jQCgvUqwET9xl4xKlXoP9TsCKzuzetoAoKpV
JezEGjIjtTOMTguVKk1ITLOQ
=VRU7
-----END PGP SIGNATURE-----
 
Thanks...this partially work...if one of the people has to work split shifts
the result will only show one of the shifts on a particular date. Any
suggestions?
 
How about a form that looks like a calendar. You select the Year and Month
and thenselect the person. The calendar will show you the hours the person
is suppose to work each day on the calendar. You canadd/edit and delete the
person.s schedule in the calendar form. You can also printout the person's
schedule in a report that looks like a calendar. If you are interested in
having me implement this into your database, send me your email address to
my email address below and I will send you a screen shot of the calendar.
 
Back
Top