Null value for no date entry for specific user . Want to separate present / absent student of any date (1 Viewer)

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
I want to separate Absents and present students of any date which I will select from a form.

I have two tables

  • userInfoTbl
  • AttendanceTbl
UserInfoTbl contains all students records their ID, Name, Registration, contacts etc.

AttendanceTbl generates the CheckIn time stamp when user Punch biometric machine. This record is linked to their “User_ID”.

Record is saved who has punched their finger, but what about those who have not punched.

Want to have a query, a field “Status” of Present “P” and Absent “A”. So I can separate records who is absent today or any specific date and who is present.

To simulate what I want.. I made a picture below for better understanding.

Can some one write query sql statement for me, I am little bit new to this field.

Thanks
1632211749058.png
 

Ranman256

Well-known member
Local time
Today, 11:28
Joined
Apr 9, 2015
Messages
4,339
Make an outer join query.
dbl-click the join line,
show all records in the student table, some records in date table.
Add field: iif(isnull([datefld],”A”,”P”))
 

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
Make an outer join query.
dbl-click the join line,
show all records in the student table, some records in date table.
Add field: iif(isnull([datefld],”A”,”P”))
Thank you I tried, but problem here is "It shows only Present records", "P" but not absent records. There is no "A"
In picture below, UserID 3 and 5 were absent on 21-September, So there is no empty record for 21 september for that user, which show up as "A" in Status field.
1632216363167.png
 

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
Thank you I tried, but problem here is "It shows only Present records", "P" but not absent records. There is no "A"
In picture below, UserID 3 and 5 were absent on 21-September, So there is no empty record for 21 september for that user, which show up as "A" in Status field.
View attachment 94632
 

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
Probably, Date range has to be used. So that query should bring all the dates, any missing "CHECKTIME" date field for any user should be recorded as Zero, or something else. Then this could be marked as Absent "A"
But can we do it?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:28
Joined
Sep 21, 2011
Messages
14,044
How can you find records which do not exist?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:28
Joined
May 7, 2009
Messages
19,169
see Query2.
 

Attachments

  • skool_bukol.accdb
    644 KB · Views: 381
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
How can you find records which do not exist?

How can you find records which do not exist?
Thats the question. The key concept is here...
Because it will be based on sequential date range for example 01.01.2021 to 31.12.2099

So any record, meaning user stamps "CHECKTIME" here in this table, is missing for any user might be marked as Null infront of user id....
This null will lead to Absent "A" in another field of "status"
But how can implement this, this the question.

Some users are coming regularly, so user time stamp "checktime" is there, while some users dont have some of those.
Then those are absent.
 

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
Thank you very much @arnelgp . Nicely done, It is perfectly working. Wish you great day.
see Query2.
Hi @arnelgp , I am here again. I am thankful you always helped with very good and practical solution.
Now I have problem about above method of marking Absent / Present.
With fewer students like above 7- students it was working perfect. In my case I had more than 200 students, query takes a lot of time to show results. Time is around 60 to 90 seconds.

Although I have date range filter on form, even if I am filtering absents of today which are around 18-19, but still because query has to look into all it takes a lot time.
1) form itself takes more than 1min, then on the top it when we use date filter then again its takes another around 90 seconds.
2) Similarly on other forms like Dashboard If link summary of attendance , For example
Total Present =...
Total Absent = ...
Total sudents =....
This form also responding the same way.
Do you have any suggestion?

Sometimes database even wont respond or takes around 3minutes.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:28
Joined
May 7, 2009
Messages
19,169
instead of building "all days" in query, you save it to table (see allDays table, these has days from jan 1, 2021 upto dec 31, 2030).
all queries now starts with "qry_". so your attendance query is qry_attendance.
 

Attachments

  • skool_bukol.accdb
    816 KB · Views: 440
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 16:28
Joined
Apr 7, 2020
Messages
280
instead of building "all days" in query, you save it to table (see allDays table, these has days from jan 1, 2021 upto dec 31, 2030).
all queries now starts with "qry_". so your attendance query is qry_attendance.
Thank you very much. great love.
 

Users who are viewing this thread

Top Bottom