Calculating number of hours

[maheshchandra]

Hi

I am creating a database where in I keep track of student and tutors
information. A tutor gives tutoring to students in groups and
individually. I plan to keep track of tutors hours as I pay the tutor
on an hourly base.

As of now I have the following tables, TUTOR, STUDENT, MEETINGS. The
meetings table has TutorID, StudentID, Date, TimeIn and TimeOut.

I need to track Tutor's hours and students attending the tutor.

Is this possible. If yes, how do I need to form the tables.

Thanks

 

[Allen Browne]

See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[maheshchandra]

Dear Allen

Thanks for the help. Your formula works perfect.

But I have small problem,.When a tutor has 3 students in his/her class,
I am having 3 records for the same tutor and its displaying 3 hours
instead of 1 hour.

Thanks

Mahesh

 

[Allen Browne]

Okay: if you work with groups in a class, you need a different table
structure.

You probably have a table of tutors, and a table of students. I suggest you
combine these into ONE table of people, with a new field to indicate if the
record is for a Tutor or Student. (You can then create a query to give you
the students, and use it anywhere you currently use your Student table.)

Now the Meeting table will need fields:
MeetingID Autonumber Primary key
MeetingStart Date/Time Date and time the meeting began
MeetingEnd Date/Time Date and time the meeting ended

The next table defines who attended the meeting, so the MeetingPerson table
will have fields like this:
MeetingPersonID Autonumber Primary key
MeetingID Number Relates to Meeting.MeetingID
PersonID Number Relates to Person.PersonID
RoleID Text Drop-down list: "Student" or
"Tutor"

If the meeting involves one tutor and one student, there will be 2 records
in the MeetingPerson table. If there are 3 students and a tutor, there will
be 4 records.

You can now create a query using the Person, Meeting, and MeetingPerson
tables.
Depress the Totals button on the toolbar.
(Access adds a Total row to the grid.)
Group By PersonID.
Type this expression into the Field row, and choose Sum in the Total row:
DateDiff("n", [MeetingStart], [MeetingEnd])
This gives the right total for each person, and you can add whatever
criteria you like, such as only a particular tutor or date range.

Perhaps the suggestion of MeetingPerson.RoleID needs explaining too. Betty
might be a tutor who is also taking lessons from Alice. That means in some
meetings Betty has the role of "tutor", and in others she has the role of
"student." MeetingPerson.RoleID lets you specify the actual role the person
had in that particular meeting. In the query that calculates how much to pay
each tutor, you add criteria on the RoleID to limit it to only the tutors.

So, there will actually be a little lookup table to hold the 2 value records
for the roles. This Role table will have just one field:
RoleID Text Primary key

 

[maheshchandra]

Hi Allen

Thanks for the formula. It works perfect.

How do I calculate total hours in the report.

Mahesh

Okay: if you work with groups in a class, you need a different table
structure.

You probably have a table of tutors, and a table of students. I suggest you
combine these into ONE table of people, with a new field to indicate if the
record is for a Tutor or Student. (You can then create a query to give you
the students, and use it anywhere you currently use your Student table.)

Now the Meeting table will need fields:
MeetingID Autonumber Primary key
MeetingStart Date/Time Date and time the meeting began
MeetingEnd Date/Time Date and time the meeting ended

The next table defines who attended the meeting, so the MeetingPerson table
will have fields like this:
MeetingPersonID Autonumber Primary key
MeetingID Number Relates to Meeting.MeetingID
PersonID Number Relates to Person.PersonID
RoleID Text Drop-down list: "Student" or
"Tutor"

If the meeting involves one tutor and one student, there will be 2 records
in the MeetingPerson table. If there are 3 students and a tutor, there will
be 4 records.

You can now create a query using the Person, Meeting, and MeetingPerson
tables.
Depress the Totals button on the toolbar.
(Access adds a Total row to the grid.)
Group By PersonID.
Type this expression into the Field row, and choose Sum in the Total row:
DateDiff("n", [MeetingStart], [MeetingEnd])
This gives the right total for each person, and you can add whatever
criteria you like, such as only a particular tutor or date range.

Perhaps the suggestion of MeetingPerson.RoleID needs explaining too. Betty
might be a tutor who is also taking lessons from Alice. That means in some
meetings Betty has the role of "tutor", and in others she has the role of
"student." MeetingPerson.RoleID lets you specify the actual role the person
had in that particular meeting. In the query that calculates how much to pay
each tutor, you add criteria on the RoleID to limit it to only the tutors.

So, there will actually be a little lookup table to hold the 2 value records
for the roles. This Role table will have just one field:
RoleID Text Primary key

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen

Thanks for the help. Your formula works perfect.

But I have small problem,.When a tutor has 3 students in his/her class,
I am having 3 records for the same tutor and its displaying 3 hours
instead of 1 hour.

Thanks

Mahesh

 

[Allen Browne]

Put a text box in the Report Footer section, and set its Control Source to:
=Sum(DateDiff("n", [MeetingStart], [MeetingEnd])) / 60
That gives hours and fractions of an hour.

The suggested article explains how to do that in a query, and express in
hours and minutes if you need that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

Thanks for the formula. It works perfect.

How do I calculate total hours in the report.

Mahesh

Okay: if you work with groups in a class, you need a different table
structure.

You probably have a table of tutors, and a table of students. I suggest
you
combine these into ONE table of people, with a new field to indicate if
the
record is for a Tutor or Student. (You can then create a query to give
you
the students, and use it anywhere you currently use your Student table.)

Now the Meeting table will need fields:
MeetingID Autonumber Primary key
MeetingStart Date/Time Date and time the meeting began
MeetingEnd Date/Time Date and time the meeting ended

The next table defines who attended the meeting, so the MeetingPerson
table
will have fields like this:
MeetingPersonID Autonumber Primary key
MeetingID Number Relates to Meeting.MeetingID
PersonID Number Relates to Person.PersonID
RoleID Text Drop-down list: "Student" or
"Tutor"

If the meeting involves one tutor and one student, there will be 2
records
in the MeetingPerson table. If there are 3 students and a tutor, there
will
be 4 records.

You can now create a query using the Person, Meeting, and MeetingPerson
tables.
Depress the Totals button on the toolbar.
(Access adds a Total row to the grid.)
Group By PersonID.
Type this expression into the Field row, and choose Sum in the Total row:
DateDiff("n", [MeetingStart], [MeetingEnd])
This gives the right total for each person, and you can add whatever
criteria you like, such as only a particular tutor or date range.

Perhaps the suggestion of MeetingPerson.RoleID needs explaining too.
Betty
might be a tutor who is also taking lessons from Alice. That means in
some
meetings Betty has the role of "tutor", and in others she has the role of
"student." MeetingPerson.RoleID lets you specify the actual role the
person
had in that particular meeting. In the query that calculates how much to
pay
each tutor, you add criteria on the RoleID to limit it to only the
tutors.

So, there will actually be a little lookup table to hold the 2 value
records
for the roles. This Role table will have just one field:
RoleID Text Primary key

Dear Allen

Thanks for the help. Your formula works perfect.

But I have small problem,.When a tutor has 3 students in his/her class,
I am having 3 records for the same tutor and its displaying 3 hours
instead of 1 hour.

Thanks

Mahesh

Allen Browne wrote:
See:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html


I am creating a database where in I keep track of student and tutors
information. A tutor gives tutoring to students in groups and
individually. I plan to keep track of tutors hours as I pay the
tutor
on an hourly base.

As of now I have the following tables, TUTOR, STUDENT, MEETINGS. The
meetings table has TutorID, StudentID, Date, TimeIn and TimeOut.

I need to track Tutor's hours and students attending the tutor.

Is this possible. If yes, how do I need to form the tables.

Thanks