COVID-19 (1 Viewer)

pekajo

Registered User.
Local time
Tomorrow, 00:37
Joined
Jul 25, 2011
Messages
133
Hi,
I have written a database for a local community house that run courses for locals. I now need to account for convid19 in that if someone has attended a course I need to be able to identify all the people they came in contact with.
I have attached a database with the 3 main table to give an idea of the current state. This is a sample with all active data removed but has the tables and relationships.
I am looking for 2 reports. A 'long version' that will list everyone who has contact with the effected student (say student 'A') even if they appear multiple times as the courses are run weekly.
Then the 'short version' where duplicates are removed, that is we only need one listing per student even though they have come in contact multiple times.
Thanks for any help
Peter
PS I find date fields notoriously hard to work with so when I add to the Attend table there is a field CCDDMMYYYY which the course ID and the date.
 

Attachments

  • Database1.accdb
    428 KB · Views: 131

plog

Banishment Pending
Local time
Today, 08:37
Joined
May 11, 2011
Messages
11,613
What have you tried? This is really a trivial report to generate. You identify your covid students then run a query to find out what other students have the same CID/ADate values as any of the covid students.

Give it a shot then ask specific questions when you run into issues.
 

pekajo

Registered User.
Local time
Tomorrow, 00:37
Joined
Jul 25, 2011
Messages
133
Hi,
Thanks for the reply. I must be missing something or making it complected. |
I believe that there are 2 questions to ask. 1. Who the student is and 2. who else attended, as the student has attended a number of courses during the period.
If I create a query with a student number I will only get that student information with the course information, how do I then 'bend' the query to include all the other student attending the same course?
I had thought of running a loop though the Attend tagging the student and each course attended then using the CCDDMMYYYY in a query but thought that there maybe an easier way of doing it.
Peter
 

plog

Banishment Pending
Local time
Today, 08:37
Joined
May 11, 2011
Messages
11,613
This only requires queries, 2 to be exact. The first query will identify the dates/classes the covid student(s) attended. You will then take that query and JOIN it to your attendance table by class and date to find all students who attended those classes on those dates who is not the covid student(s).
 

pekajo

Registered User.
Local time
Tomorrow, 00:37
Joined
Jul 25, 2011
Messages
133
Hi,
Thanks for your help. Worked like a charm. I think I was over complicating the issue.
Peter
 

Users who are viewing this thread

Top Bottom