Instead of

....
FROM TimeRecords AS T, Employee AS e, Depart AS D
WHERE (((T.emp_id)=[E].[emp_id]) AND ((E.depart_id)=[d].[depart_id]) AND
((T.mark) Is Not Null) AND ((T.sign_time)=[E].[hire_date]))
....


Try making a query, q, which pumps the required data, but limited to the
earliest and latest dateStamp:


SELECT T.emp_id, e.emp_name, t.card_id, d.depart_name, MIN(t.sign_date),
MAX(t.sign_date)
FROM TimeRecords AS T, Employee AS e, Depart AS D
WHERE (((T.emp_id)=[E].[emp_id]) AND ((E.depart_id)=[d].[depart_id]) AND
((T.mark) Is Not Null) AND ((T.sign_time)=[E].[hire_date]))
GROUP BY T.emp_id, e.emp_name, t.card_id, d.depart_name


And use that query (which already have only the minimum and maximum
sign_date) to built your crosstab (if it is still required to have a
crosstab).



Vanderghast, Access MVP


"Peter Kolbe" wrote in message
...
Hi
I have a fingerprint time attendance system running, with its software
that uses a microsoft access db.
it currently has a crosstab query that results as follows (See below)

I am only interested in the first time, and the last time for that day (to
calc hours at site),
but it goes and increments the column each time a finger is scanned (as
you can see below)

How can I make another query that will only work on the first time (colum
1), and the last time (whatever is the last colum that has the time in.)

number NAME CARDID DEPARTMENT DATE RECORDS 1 2 3 4 5
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/04 2 07:20:59
13:09:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/12 5 08:35:59
08:55:59 09:24:59 15:52:59 16:17:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/13 2 08:57:59
12:54:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/14 4 08:20:59
13:30:59 15:24:59 16:11:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/18 2 08:27:59
16:56:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/19 2 08:13:59
16:36:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/20 2 08:21:59
16:18:59
4 Nicholas Grignard 0000000004 Jireh Electrical 2010/05/21 1 08:16:59
5 SP 0000000005 Jireh Electrical 2010/05/12 2 08:39:59 16:18:59
5 SP 0000000005 Jireh Electrical 2010/05/13 2 08:57:59 16:21:59
5 SP 0000000005 Jireh Electrical 2010/05/14 2 08:21:59 16:11:59
5 SP 0000000005 Jireh Electrical 2010/05/17 2 08:16:59 16:32:59



The SQL is as follows :\

TRANSFORM First(CStr(CVDate(T.sign_time-Int(T.sign_time)))) AS sign_time
SELECT T.emp_id AS [number], E.emp_name AS NAME, T.card_id AS CARDID,
D.depart_Name AS DEPARTMENT, CVDate(Int([T].[sign_time])) AS [DATE],
Max(T.mark) AS RECORDS
FROM TimeRecords AS T, Employee AS E, Depart AS D
WHERE (((T.emp_id)=[E].[emp_id]) AND ((E.depart_id)=[d].[depart_id]) AND
((T.mark) Is Not Null) AND ((T.sign_time)=[E].[hire_date]))
GROUP BY T.emp_id, E.emp_name, T.card_id, D.depart_Name,
CVDate(Int([T].[sign_time]))
ORDER BY T.emp_id, CVDate(Int([T].[sign_time]))
PIVOT T.mark;



Thanks

Peter