C
Christine Smith
Hello.
In my database I have four tables.
Participants, Participants_Module_Records, Module_Occurrences, Modules
The relationships are:
Participants ---- Participants_Module_Records 1-- ¥ as 1 participant can do 1 or more modules
Module --- Module_Occurrences 1 -- ¥ as 1 module has 1 or more occurrences
Module_Occurrences ---- Participants_Module_Records 1--¥ as 1 module occurrence has 1 or more participants taking the course
Hence
Participants 1--¥ Participants_Module_Records ¥ --1 Module_Occurrences¥ --1 Module
We need to be able to select which participants have done a particular module say Module D and then show whether the participants have also done modules A, B or C. I want to create a report that outputs (on a single line for each participant) their name and whether they have done a particular module.
I have created a report, based on a query, which selects the participants that have done say Module D. However my problem is with getting the module history on one line of the report. The problem is the 1 to many relationship between the Participants and Participants_Module_Records tables. So if a participant has done two modules he therefore has 2 records in the Participants_Module_Records table and 2 lines are produced on the report.
I have managed to create the report by selecting the participants which have done Module D and then using Dlookup fuctions to determine whether the participants have done module B, C , D but this seems over complex.
My questions are:
Is my table / relationships correct
Is there something obvious I’m missing with the report
Thanks for any help
Christine
In my database I have four tables.
Participants, Participants_Module_Records, Module_Occurrences, Modules
The relationships are:
Participants ---- Participants_Module_Records 1-- ¥ as 1 participant can do 1 or more modules
Module --- Module_Occurrences 1 -- ¥ as 1 module has 1 or more occurrences
Module_Occurrences ---- Participants_Module_Records 1--¥ as 1 module occurrence has 1 or more participants taking the course
Hence
Participants 1--¥ Participants_Module_Records ¥ --1 Module_Occurrences¥ --1 Module
We need to be able to select which participants have done a particular module say Module D and then show whether the participants have also done modules A, B or C. I want to create a report that outputs (on a single line for each participant) their name and whether they have done a particular module.
I have created a report, based on a query, which selects the participants that have done say Module D. However my problem is with getting the module history on one line of the report. The problem is the 1 to many relationship between the Participants and Participants_Module_Records tables. So if a participant has done two modules he therefore has 2 records in the Participants_Module_Records table and 2 lines are produced on the report.
I have managed to create the report by selecting the participants which have done Module D and then using Dlookup fuctions to determine whether the participants have done module B, C , D but this seems over complex.
My questions are:
Is my table / relationships correct
Is there something obvious I’m missing with the report
Thanks for any help
Christine