I have a database that has a main table with 4-5 other tables related to the main table. Each of these tables is a particular class (Math, science, english etc) In each of these tables there are a number of fields that denote a lesson. As the student completes these the person checks a check box and puts a date of completion. NOTE: They completed lessons will NOT go in order...they could do lesson 1, and lesson 9.
Example: say these are the fields selected in the tables
Math
Lesson1 checkbox
Lesson1 Date
Lesson2 checkbox
Lesson2 date
Lesson5 checkbox
Lesson5 date
etc
Science
Lesson3 checkbox
Lesson3 Date
Lesson6 checkbox
Lesson6 date
etc
What i need is a report that:
1. will allow the user to input a date (start date) and then using Now() get a date range. ONLY include field data that falls within the date range.
2. In th is singlereport I need sections that represent each table/class (math, science etc.) BUT i dont want to return all the fields with their dates. ONLY the fields that checkbox "Is Not Null" and the date is within the date range specified. i dont want the report to report back the fields that do not meet this criteria.
So something like this
Report for 6/1/2011 to current:
Student info
Address
other student info
Math:
Lesson1 2-2-2012
Lesson2 2-1-2012
Lesson5 2-1-2012
Science
Lesson3 2-2-2012
Lesson6 2-1-2012
on the form I will have to start date and the Student ID number. So the query will have to refrence these...then find only the fields (lessons) that have the checkbox checked and the date completed that falls within the date range.
Do I create individual querys and then put those inside a main report? The problem is when I place the queries inside the main report I cannot specifiy breaks...I dont know how many records will be returned and they will vary for each student. so the spacing has to be dynamic. so the inserted queries dont overlap.
Should I do this in code with VBA with SQL queries?
Any thoughts on where to start would be greatly appreciated.
Thanks