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

[Ihk]

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

 

[Ranman256]

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]

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.

 

[Ihk]

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]

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]

How can you find records which do not exist?

 

[arnelgp]

see Query2.

 

[Ihk]

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]

see Query2.

Thank you very much @arnelgp . Nicely done, It is perfectly working. Wish you great day.

 

[Ihk]

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.

 

[arnelgp]

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.

 

[Ihk]

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.