query construct question

[George Applegate]

I have a two tables that look like:
Emp table Punch table
employee------------employee
lastname date
firstname time
department inorout

The link between the tables is employee

Each employee might have multiple ins and outs for a given date.
I'd like to somehow create a query that would display something like:

department employee date time in/out time in/out time in/out
(left to right have multiple times shown along with whether an in or
out.

Is this a "crosstab" query and if so, how do I code it so it
sequentially shows the time and in/out for say up to 6 or 8 punches?
If there are more than 8, ignore and go to next employee date or
employee...if less than 8 show them and go on to next employee

Query would look like:
Dpt Emp Date Time I/O Time I/O Time I/O
99 2000 01/01/08 8:30 In 11:30 Out 12:30 In...
99 2000 01/02/08 8:15 In 11:00 Out 11:45 In...
99 4000 01/01/08 8:00 In 12:00 Out 14:00 In...
display up to 6 or 8 times with corresponding in/out flag...

Or, if it's easier to somehow do it, it could go like this (and would
even be better)
Dpt Emp Date Time In Time out Time In Time out
99 2000 01/01/08 8:30 11:30 12:30 4:30
99 2000 01/02/08 8:15 11:00 11:45 16:00
99 4000 01/01/08 8:00 12:00 14:00 17:30
say up to 3 or 4 ins/out (6 or 8 total punches) displayed.

Is this possible?

thanks,
ga


George Applegate
(e-mail address removed)

 

[Michel Walsh]

Doable, yes. .


Assuming the dateStamp contains and the date and the time (of the IO)
Assuming field IO contains "I" or "O" (for in or for out)

kind of:

employee dateStamp IO
john 2001.01.01 09:00:06 i
john 2001.01.01 11:58:12 o
john 2001.01.01 12:49:16 i
john 2001.01.01 17:02:08 o


Rank the data by employee, by IO, by date, accordingly to the time, which
can be done with the following query:



----------------------
SELECT a.employee,
DateValue(a.dateStamp) AS theDate,
TimeValue(a.dateStamp) AS theTime,
a.io,
COUNT(*) AS rank

FROM tableName AS a INNER JOIN tableName AS b
ON a.employee=b.employee
AND a.io = b.io
AND DateValue(a.dateStamp) = DateValue(b.dateStamp)
AND a.dateStamp >= b.dateStamp

GROUP BY a.employee,
DateValue(a.dateStamp),
TimeValue(a.dateStamp),
a.io
-------------------------



Save it under the name, say, qr1. Then, make the crosstab:



-------------------------
TRANSFORM LAST(theTime)
SELECT employee, theDate
FROM qr1
GROUP BY employee, theDate
PIVOT io & rank IN ("i1", "o1", "i2", "o2", ... , "i8", "o8")
-------------------------



should produce something like:

employee theDate i1 o1 i2 o2
.... i8 o8
john 2001.01.01 09:00:06 11:58:12 12:49:16 17:02:08 ... null
null






Hoping it may help,
Vanderghast, Access MVP

 

[George Applegate]

Michel,

Thanks a ton for your help! You have me close, I think. The qr1
query puts out the data as you indicated

emp# date time IO rank
2000 01/01/08 11:30 IN 1
2000 01/01/08 14:40 OUT 1
2000 01/01/08 15:30 IN 2

But when I do the Crosstab, I am not getting any times to actually
show.
The Cross tab has

2000 01/01/08 blanks blanks blanks blanks, blanks
2000 01/02/08 blanks blanks blanks blanks blanks

I don't know if something is wrong with the PIVOT statement? It shows
the headings but no times in them. Otherwise it seems to be working
as advertised.

One note - the field for InOut is "InOut" and second, the value in
there is IN or OUT

I made the Pivot statement:

PIVOT InOut & rank IN("IN1", "OUT1", "IN2", "OUT2", "IN3"...

Any ideas what I did wrong? The headings are there, just no times
showing. Or is it something with my Transform statement

I am just using the field which is Logtime and it seems to be in qry1
okay. Qry1 looks exactly right. It seems to be more of a problem
with my PIVOT statement??

I get the employee # and the Log date in the crosstab query, just no
times...

Any ideas or suggestions??

By the way, THANKS A MILLION FOR YOUR HELP!!!
ga

Doable, yes. .


Assuming the dateStamp contains and the date and the time (of the IO)
Assuming field IO contains "I" or "O" (for in or for out)

kind of:

employee dateStamp IO
john 2001.01.01 09:00:06 i
john 2001.01.01 11:58:12 o
john 2001.01.01 12:49:16 i
john 2001.01.01 17:02:08 o


Rank the data by employee, by IO, by date, accordingly to the time, which
can be done with the following query:



----------------------
SELECT a.employee,
DateValue(a.dateStamp) AS theDate,
TimeValue(a.dateStamp) AS theTime,
a.io,
COUNT(*) AS rank

FROM tableName AS a INNER JOIN tableName AS b
ON a.employee=b.employee
AND a.io = b.io
AND DateValue(a.dateStamp) = DateValue(b.dateStamp)
AND a.dateStamp >= b.dateStamp

GROUP BY a.employee,
DateValue(a.dateStamp),
TimeValue(a.dateStamp),
a.io
-------------------------



Save it under the name, say, qr1. Then, make the crosstab:



-------------------------
TRANSFORM LAST(theTime)
SELECT employee, theDate
FROM qr1
GROUP BY employee, theDate
PIVOT io & rank IN ("i1", "o1", "i2", "o2", ... , "i8", "o8")
-------------------------



should produce something like:

employee theDate i1 o1 i2 o2
... i8 o8
john 2001.01.01 09:00:06 11:58:12 12:49:16 17:02:08 ... null
null






Hoping it may help,
Vanderghast, Access MVP

George Applegate
(e-mail address removed)

 

[George Applegate]

One other thing.

The employee name comes from a different table...how do I tie that in?
Do I connect that in the crosstab query or in the original query, if I
want that to show. link is on employee #

Can I do that???
thanks,
ga

Doable, yes. .


Assuming the dateStamp contains and the date and the time (of the IO)
Assuming field IO contains "I" or "O" (for in or for out)

kind of:

employee dateStamp IO
john 2001.01.01 09:00:06 i
john 2001.01.01 11:58:12 o
john 2001.01.01 12:49:16 i
john 2001.01.01 17:02:08 o


Rank the data by employee, by IO, by date, accordingly to the time, which
can be done with the following query:



----------------------
SELECT a.employee,
DateValue(a.dateStamp) AS theDate,
TimeValue(a.dateStamp) AS theTime,
a.io,
COUNT(*) AS rank

FROM tableName AS a INNER JOIN tableName AS b
ON a.employee=b.employee
AND a.io = b.io
AND DateValue(a.dateStamp) = DateValue(b.dateStamp)
AND a.dateStamp >= b.dateStamp

GROUP BY a.employee,
DateValue(a.dateStamp),
TimeValue(a.dateStamp),
a.io
-------------------------



Save it under the name, say, qr1. Then, make the crosstab:



-------------------------
TRANSFORM LAST(theTime)
SELECT employee, theDate
FROM qr1
GROUP BY employee, theDate
PIVOT io & rank IN ("i1", "o1", "i2", "o2", ... , "i8", "o8")
-------------------------



should produce something like:

employee theDate i1 o1 i2 o2
... i8 o8
john 2001.01.01 09:00:06 11:58:12 12:49:16 17:02:08 ... null
null






Hoping it may help,
Vanderghast, Access MVP

George Applegate
(e-mail address removed)

 

[Michel Walsh]

Try the crosstab without the IN list in the PIVOT:

TRANSFORM LAST(theTime)
SELECT employee, theDate
FROM qr1
GROUP BY employee, theDate
PIVOT io & rank

That MAY creates more than 8 in or 8 out column, but you can fix that later.
Be sure to take note ot the fields name created. They must be typed exactly
as they are created, if we use the IN( ) list of the PIVOT clause (as
string, ie, delimited by double quotes).



Vanderghast, Access MVP

 

[Michel Walsh]

You can make another query: bring the crosstab and the table that will
supply the name instead of the id number, and join them on their common
id_number fields.



Vanderghast, Access MVP

One other thing.

The employee name comes from a different table...how do I tie that in?
Do I connect that in the crosstab query or in the original query, if I
want that to show. link is on employee #

Can I do that???
thanks,
ga

..fmctc

 

[George Applegate]

Michel,

THANKS A TON! That did the trick. I think the problem is, I would
have to put it as "IN 1" and "OUT 1", etc. The rank is
apparently too many characters. But just doing it this way works
great!

Thanks much for all of your help. You take something extremely
complicated and make it look like a piece of cake!

ga

Try the crosstab without the IN list in the PIVOT:

TRANSFORM LAST(theTime)
SELECT employee, theDate
FROM qr1
GROUP BY employee, theDate
PIVOT io & rank

That MAY creates more than 8 in or 8 out column, but you can fix that later.
Be sure to take note ot the fields name created. They must be typed exactly
as they are created, if we use the IN( ) list of the PIVOT clause (as
string, ie, delimited by double quotes).



Vanderghast, Access MVP

George Applegate
(e-mail address removed)

 

[Michel Walsh]

You can use the VBA function TRIM to remove trailing spaces, before making
the concatenation to rank :


TRANSFORM ...
PIVOT TRIM(io) & rank


Vanderghast, Access MVP