Mark check if the data exists in another table (1 Viewer)

smd_07

New member
Local time
Today, 13:56
Joined
Oct 24, 2021
Messages
4
Hi everyone!

I am new here, I am looking for help with a task that I need to do.

I currently have a report with a list of students enrolled in a course: Student ID, name, surname, passport. This obviously comes from a table.

On the other hand, I have a table where the attendance data is saved. Line ID, Student ID, Date.

If there is class on 10/24/2021 and in this table there is a line with the student ID and this date, it means that they have come. If there isn't, he hasn't come.

The course is 5 classes.

I need to duplicate the first report where I add 5 new columns with the dates of the classes and to be able to see in the report who has come and who has not in each class.

That is, I need the first report where all the students are but then I need a check if the student has come on 10/24/2021 and later on other dates

Something like that:


How could you design the report to check if that student's line exists in the attendance table?

Thanks a million in advance
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:56
Joined
May 7, 2009
Messages
19,169
here is a sample queries and you i think it explains it.
create a report for students table, another for attendance table and
another for 3_qryCrosstab query.

create a main report and put the 3 report as sub-report.
 

Attachments

  • course.accdb
    588 KB · Views: 310

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 19, 2002
Messages
42,970
Welcome aboard:)

Unless you only ever have one and only one course, you need two more tables in your database. One to hold the list of classes and a table that connects students to classes. This is called a junction table and finally, your attendance table connects to the junction table rather than to the student table.

Once you get the tables fixed up, a crosstab query similar to the one built by arnel will give you the dsplay you wand.
 

smd_07

New member
Local time
Today, 13:56
Joined
Oct 24, 2021
Messages
4
here is a sample queries and you i think it explains it.
create a report for students table, another for attendance table and
another for 3_qryCrosstab query.

create a main report and put the 3 report as sub-report.
I'm sorry again but when I open the reports in the example that you gave me, debugging errors appear
 

smd_07

New member
Local time
Today, 13:56
Joined
Oct 24, 2021
Messages
4
I am thinking in another way

suppose I have a text field in a report

How would the sql query or what should be done to make a select and look for the student in the attendance table and if it is on a certain date, put an X?

I imagine that with an IIf you can get it?

Thanks in advance
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:56
Joined
May 7, 2009
Messages
19,169
what version of Access do you have?
here is an A2007 sample:
 

Attachments

  • course_a2007.accdb
    468 KB · Views: 357

Users who are viewing this thread

Top Bottom