A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Extracting from Crosstab query



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2010, 09:13 AM posted to microsoft.public.access.queries,microsoft.public.access.reports
Peter Kolbe
external usenet poster
 
Posts: 11
Default Extracting from Crosstab query

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

  #2  
Old May 25th, 2010, 01:26 PM posted to microsoft.public.access.queries,microsoft.public.access.reports
vanderghast
external usenet poster
 
Posts: 593
Default Extracting from Crosstab query

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


  #3  
Old May 25th, 2010, 01:36 PM posted to microsoft.public.access.queries,microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Extracting from Crosstab query

Instead of a Crosstab, how about using a simple aggregate (totals query)
SELECT T.emp_id AS [number]
, E.emp_name AS EmpNAME
, T.card_id AS CARDID
, D.depart_Name AS DEPARTMENT
, DateValue([T].[sign_time]) AS [WorkDay]
, Max(TImeValue(sign_Time)) as TimeOut
, Min(TimeValue(sign_Time)) as TimeIn
, 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 AS [number]
, E.emp_name
, T.card_id
, D.depart_Name
, DateValue([T].[sign_time])
ORDER BY T.emp_id, DateValue([T].[sign_time])

Also for efficiency I would use joins rather than criteria in the where clause
SELECT T.emp_id AS [number]
, E.emp_name AS EmpNAME
, T.card_id AS CARDID
, D.depart_Name AS DEPARTMENT
, DateValue([T].[sign_time]) AS [WorkDay]
, Max(TImeValue(sign_Time)) as TimeOut
, Min(TimeValue(sign_Time)) as TimeIn
, Max(T.mark) AS RECORDS
FROM (TimeRecords AS T INNER JOIN Employee AS E
ON T.emp_id=[E].[emp_id])
INNER JOIN Depart AS D
ON E.depart_id=[d].[depart_id]
WHERE T.mark Is Not Null
AND T.sign_time=[E].[hire_date]
GROUP BY T.emp_id AS [number]
, E.emp_name
, T.card_id
, D.depart_Name
, DateValue([T].[sign_time])
ORDER BY T.emp_id, DateValue([T].[sign_time])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Peter Kolbe wrote:
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.