how to perform particular calculation in query

[Guest]

Hi.

I currently have a table in which each record is based on a particular
attendance event (students attend a language lab at my university). In each
record, I have the date, student, professor, time in, and time out. In the
query, I'd like to see each student's daily, weekly and semester-long totals
of lab attendance. Any help on how to do this would be greatly appreciated.

Thanks,
Mike

 

[Allen Browne]

Presumably the [time in] and [time out] fields are Date/Time type fields,
and they contain both the date and the time. You can therefore use
DateDiff() to get the duration in minutes.

To get the sum of the minutes the student actually attended in the date
range:
1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

3. Drag Student into the grid.
In the Total row under this field, accept Group By.

4. In a fresh column in the Field row, enter:
Minutes: DateDiff("n", [time in], [time out])
In the Total row under this field, choose Sum.

5. Drag the [Time In] field into the grid.
In the Total row, choose Where
In the Criteria row, enter the dates for your week/semester whatever.

If you are trying to get the various results (weekly, daily, ...) in one
query, base the query on the Student table (so you get one record per
student) and use a series of subqueries to get the duration in mintues for
each period. There's an introduction to subqueries here, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you are trying to compare the students attendance per course against the
expected attendance per course, you will need several more tables that
define the course, enrolment, sessions for each enrolment, and you can then
compare actual attendance against sessions.