query for absent employee

[koje]

Hi,
I have to create report for only absent employee.
there are two tables
table1(employee id,date in,date out,description)
table2(employee id,description)
-----------------------------------------
The data in table1 I get it monthly from id card reader for attandence ,but the problem it sometime record only date in time and other time date out time.

I tried all thoese but its not work :

SELECT [Employee id]
FROM [table2]
WHERE ([Date in time] Is Null) And ([Date out time] Is Null);
-----------------------------------------------------
select * from Table2
left join table1 on table2.[employee id]=table1.[employee id]
where table1.[employee id] is null;
-----------------------------------------------------
SELECT Table2.*,
(select top 1 [date in] from table1) AS [Date]
FROM Table2 LEFT JOIN Table1 ON Table2.[employee id] = Table1.[employee id]
WHERE Table1.[employee id] Is Null;

------------------------------------------------------
I need a correct query that show:

only absent employee and the date.

(I should also run this query also for previous 3 months)

Thanks

 

[jal]

I don't understand your tables.
I don't get how a record in table1 is supposed to indicate absence.
If that record has no date at all (if the 2 date/time fields are null), how are you supposed to determine which date of absence this record is indicating?

IN other words I would have expected your table to look more like this:

Date Time In Time Out

meaning that the Date column is never null.

 

[jal]

Anyway, here is a general outline of a possible solution to such a problem (this won't work due to the problem I mentioned but maybe it will give some hints).

SELECT [Employee ID], Absences.Status
FROM
(
SELECT [Employee ID], IIF([Date in Time] IS Null AND [Date Out Time] is Null, "Absent", "Present") AS Status
FROM Table1
) AS Absences
WHERE Absences.Status = "Absent"

 

[koje]

Thanks Jal,but I mean I need the exact date when the employee absent

For example:
Tom was absent on 2-1-2007 , so there is no date in or date out ( these data came from ID card reader machine which is somethime miss one of these recordes date timein or date timeout). datein or dateout mean the employee is present not absent. if there is no datein and no dateout than he is absent
----------------------------------------------
table1 (attendence table) have these info:

Name description DateIN time Dateout time
TOM IT 1-1-2007 1-1-2007
TOM IT 3-1-2007 3-1-2007
TOM IT 4-1-2007 - -------
TOM IT ------- 5-1-2007

--------------------------------------------------
I need this output form the query:
Name: Absent date Descripton
TOM 2 -01-2007 IT
----------------------------------------------------
table2 (usernamelist)
Name Description
Tom IT
-------------------------------------------------
ONLY 2 TABLES IN ACCESS
TABLE1 (NAME,DATEIN,DATEOUT,DESCRIPTION)

TABLE2(NAME,DESCRIPTION)
----------------------------------------------

(note I replaced employeeid with name)

Thanks alot

 

[khawar]

Query For Absent Employess

Download the attached sample


Regards

Khawar

 

[koje]

You are my hero. Thanks so much for your help.

But could you please explain it to me. I want to see the query also how did you do it? or I will not be able to use it. (BTW Im beginner)

 

[khawar]

It has been done using five queries press the shift key while opening then check all queries step by step as i have written step1 step2 and so on with name of queries

Cartesian Product has been used to generate all dates between the date range provided through form (form1) in the first query(step1) and these dates are later on used for getting absent dates for employees

Regards

Khawar

 

[koje]

Khawar ,

when I tried to import the attendence excel sheet to MSAccess than I run the query it gave me wrong output (all users names appeared as absent)


Could you please check the attachment? (note that all the data in the excel should be import to access)

Thanks again

 

[khawar]

Sorry i forgot to remove the time portion from attendance date

check now its working


Khawar

 

[koje]

Thank you khawar ! God bless you.

your the best.