Creating a Report from multiple tables with multiple dates

[drnld]

Hi - I need help figuring out how to create a report using a query & a table at the same time or if that's even the way I need to go about it.



I am using the Student Database template installed on the computer as my base template and adding tables/queries for our specific needs. I added a mentor report table by copying the student attendance table so it would function the same.

These are the fields I set up in the Mentor Report Table:

ID (not primary key - was causing errors)
Student (look up in student table)
Report Date (set to default to today's date)
Progressing (yes/no)
Academic (yes/no) *yes = having problems
Shop (yes/no) *same as above
MComments (memo)
Verbal (date)
Written (date)

I also have another table "Student Attendance" that was part of the original database which I edited some existing fields. This works perfectly right now. The fields are:
ID (primary key)
Student (number-lookup from Students Extended query)
Hours (number)
Status (created look-up for "Present", Absent", "Termed", "LOA")
Comments
Late (yes/no)
Early (yes/no)
Reqhrs (number - defaults to 4)

Lastly, the Student Table includes a look up field called [cycle] that looks up what cycle they are in which includes a start, end, internship and graduation date for that specific cycle.

What I need to be able to provide is a dated weekly report that totals the amount of hours each student has attended and missed for their specific date range to date and that gives me all the fields from the mentor report table. Since their are multiple cycles happening at the same time, the number of actual hours attended and missed for each student would vary depending on their start date.

I have tried all sorts of multiple queries and can't get the records to display what I need. I don't write code and so I'm wondering if someone can help. Is this even possible with the way things are set up?

I have a deadline to provide this by Thursday this week. Is this possible?

Any help or suggestions would be appreciated.

Dottie

[ranman256]

Yes you can query: tables & querys, or querys & querys, etc.

You would bring all tables needed (they should link on their common keys: Student to Student, etc.)
If you were only working on the current Cycle, then a query of STUDENT with a date range may be better...qsStudentsCurrent

I'd say, bring out Attendance and qsStudentsCurrent then sum the [hrs] field from attend.