Calculating total fields?

[ptlm65]

I have a query that has a sum total of the entire colomn in a field. Ho
do I create a query that subtracts that sum total from a field in
different table?

What I am working with is:

tblStudents
HoursGiven

tblHours
DateWorked
HoursWorked

Query1 totals the HoursWorked in the tblHours table but I don't kno
how to subtract that total from HoursGiven in tblStudents. Can anyon
help? Thank you

 

[Jeff Boyce]

Given the table structure you described, the best you could hope for is a
"global" hours worked from Total student HoursGiven. I don't see any
(other) connection between the two.

Are you trying to show how many hours a student has left? If so, your
second table needs to record which student the hours worked are for.

 

[ptlm65]

Yes, Sorry I wasn't specific. My two tables are:

tblStudents
Name
Hoursgiven

tblHours
DateWorked
HoursWorked
Name (Foreign Key)

When I go to tblStudents I can click the plus sign and see a drop-dow
list of dates worked and hours worked on those dates.

Now all I need to do is be able to enter the date and hours worked in
form and have it subtracted from hours given, giving me a total hour
remaining.

 

[Jeff Boyce]

A few points...

I personally know of two fellows named Lynn Johnson ... one is a co-worker,
the other is a felon, wanted by the police in another part of the state.
And if sharing a name isn't enough, both share the same birthday! You do
NOT want to use StudentName as a primary key.

The word "Name" is a reserved word in Access -- how is Access to know
whether you are referring to its own internal property or your definition?
(and how are you to know?!)

If you are "going to the table" to look for data, you will run into
difficulties. In Access, tables store data, forms (and reports) display
data, and queries retrieve data. Using tables as if they were spreadsheets
will cause you no end of headaches!

One approach to what you've described would be to use a query to return
student information, then build a form based on that query. Next, build a
query that retrieves Hours Worked information, and a form on that query.

Open the first form in design mode and add the second form to it as a
subform. Use the subform control's Parent and Child properties to connect
the two forms. Add a pair of unbound controls to the main (student info)
form. One will display the sum of student hours for that student. One will
display the difference between HoursGiven (student info) and the sum of
student hours for that student.

Does this match what you were trying to do?