Multiple records validating a number off another record

[Chevy757IT]

I have a table that states how many hours it takes to teach a course.

Then I have another table where my instructors are going to come in and input how many hours they have instructed, some courses have multiple instructors teach a lesson.

What I want is when the instructors input their hours it adds them all up for each course date and validates it to see if the max has been reached.

So say instructor "A" inputs he instructed 40 hours and instructor "B" instructed 50 hours and the course is only 80 hours course then when the second instructor inputs his hours it has an error.


I currently have a table with general course information that gives the name of the course and then how many hours it is.



I have a table which has the course schedule that has the course name, course serial number and dates for when it convenes and grades

I have a table that the instructor inputs their hours based on a look up to the course schedule.

How would I go about doing this? I searched the forum and couldn't find the right answer.

[June7]

So each instructor hours input would be a separate record?

Can build an aggregate GROUP BY (Totals) query that sums the hours for each course. Join this query to the Courses table by joining on the CourseID fields. This will put the course authorized hours and the instructor input hours side-by-side for review.

If you want this hours check to occur in real-time while instructors input their hours, will probably need to use VBA code.

[Chevy757IT]

I am in the US Navy and not a expert at Access. How do you go about building an aggregate Group by totals. Then how to join it to Courses table?

[Chevy757IT]

Yes each instructor would record their hours for each course on a separate record.

[June7]

Use query builder. Really basic Access functionality. Access Help has guidelines for building an aggregate query. Join aggregate query to Courses table in another query.