create an unmatched query based on the date

[EileenJohn]

Hi, I'm still new to Microsoft access. I'm trying to create a database monitor students attendance. I have 2 sheet.
Sheet 1 (keep the student list): student id, student name, address class name
sheet 2 (keep the student attendance record): date, student id, student,name, class name
I try to create a query to "find no. of students that did not attend the class based on the date".
I did try to create a query using unmatched wizard but it didn't give me the result that I want.
Any ideas? Thanks in advance.

 

[Joe4]

Can you post the SQL Code of the query you came up with that isn't working? That will give us an idea of how you have named your objects, and allow us to give you code that may more closely resemble your structure. You can just switch your query to SQL View and copy and paste it here.

Also, can students be registered for more than one class?
If so, then you probably have students listed more than once in your first table, which really is not a normalized database structure. Your student table should have each student listed exactly once. You would then have a separate table for which classes they are registered in (student id, class id). You would probably also have a separate table for classes which includes class id and class name.

So, these counts you are trying to get, are you trying to get them for each class?

 

[EileenJohn]

Yes ,for each class if possible. For eg: In math class, 68 students registered.
(Student id, student name)
then I also have weekly attendance sheet ( date, student id , student name )
so I linked the 2 sheets to access and using query wizard to find unmatched query.
Every week I need to check on this date, how many students DID NOT come to class.
sql:
SELECT weeklyattendance . Date , weeklyattendance . Student_id, weeklyattendance . Student_name FROM weeklyattendance LEFT JOIN registered ON weeklyattendance .[student_id]=registered.[student_id] where (((registered.student_id)is null));

when I run the sql, it shows blank .

What at should I do?

 

[Joe4]

You have your LEFT JOIN going the wrong way. You want the people who are in the registered table who are not in the weeklyattendance table, i.e.

SELECT registered.Student_id, registered.student_name
FROM weeklyattendance RIGHT JOIN registered ON weeklyattendance.[student_id] = registered.[student_id]
WHERE (((weeklyattendance.student_id) Is Null));

Note: One other thing. Since you have a Student table that already has the name in it, the name should not also be in the registered table. If you want to return the name, just include the Student table in the query also (you should not have redundant fields in non-key fields).

 

[EileenJohn]

Where should I put the date? I want to include date so it is easier for me to make a graph in excel. Weekly graph.

 

[Joe4]

The query returns a list of names. What exactly do you intend on graphing?
Where/how are you specifying what date range you want to return the values for?

 

[EileenJohn]

I forgot to mention that there are batch A,B,C,D enroll for bio class. So in registered sheet, basically : student ID, name, batch, and date(at which date they should attend the class). Every time they come to class, they need to scan their ID. So the attendance sheet is automatically update, which is why I linked the attendance sheet to access.
as for graph, I need to see the pattern, at which date and which batch that mostly escape the class.
So, how I should do the query? I try to rough join link the date from registered sheet with attendance date but it give me blank result. I guess I do it the wrong way.

 

[Joe4]

So, since you are looking to graph, is it fair to say that you are actually looking for counts, and not names?

 

[Joe4]

I think it would be very helpful to see the following.

- A small data sample of each of your tables
- Your expected output based on those two samples

 

[EileenJohn]

Register.xls
StudentID StudentName Date Batch
18515 John. 28/3/17. A
......
18234 Lucy. 28/3/17. B
..........
13475 Adam. 29/3/17. C
45314. James. 29/3/17. C
......

WeeklyAttendance.xls
StudentID. StudentName. Date. Batch
18515. John. 28/3/17. A
13475. Adam. 29/3/17. C
and so on....

Graph ( Not attend class)
1. Count total student not attend class vs batch. Eg: when I filter the date 28/3 it will display graph that count total batch A and B.