Subform calculation issue

[Guest]

I am very close but missing a piece on my database. I am creating a
professional growth database. It has two tables. Employees (basics) and PG
(courses taken), In the courses taken I have credit hours, credits,
enrichment, and enrichment hours, as well as the course information.

I have a form for entering data information. The tables are linked by the
PG ID. I have added a subform that has all the courses listed that an
employee has taken.

I then added a subform that totaled the credits in a query. I decided to
add fields that show which were used "credits used", "credit hours used",
etc. Then I added a calculated field that subtracted the credits used from
the sum and leaves a balance. I then added an increment field and a
calculated field that multiplies the increment by $35.

Here's the problem, when I enter the credits used increments, it enters the
same information for all employees. Should I create a third table called
Credit and link them to the employee by ID? Should I just add these fields
to the employee table? Any suggestions? It looks good, seems right, but
somehow it is not linked to an employee.

Thank you.

 

[Al Campagna]

Sprowler,
The (ex. name) CreditUsed control is "unbound" to any field, so it will
show the same value on all records.
Since it seems as though your using the CreditUsed value as an element in
a calculation, (or logic decision) you should be able to just add a
CreditUsed field to the same table as the subform is based on... and store
the value. Once "bound"... each record can have it's own CreditUsed.
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Here is what I figured out on a sample. I will add those fields to the
course table. Then I can have a subform listing the courses taken. And from
the same table, I will have a second subform that has the credits. I can sum
up each of the 4 categories (credits, credit hours, enrichment, enrichment
hours). And I will show the sum and then I can enter the credits used and
create a credits remaining in that subform. I can add the increment and
create the value of each increment. I tried it out and it seems to work. Is
this what you were thinking?

 

[John W. Vinson]

I then added a subform that totaled the credits in a query. I decided to
add fields that show which were used "credits used", "credit hours used",
etc. Then I added a calculated field that subtracted the credits used from
the sum and leaves a balance. I then added an increment field and a
calculated field that multiplies the increment by $35.

Please post the actual expression you used, and indicate where you used it.

This total should NOT be stored in any table (unless you want to be able to
edit it so that its value is different than the calculated value); it should
be recalculated as needed. I'm guessing that to get the value associated with
each employee, it should be calculated *in a query* rather than on a form -
but not knowing the nature of the data nor of the calculation that's just a
guess!


John W. Vinson [MVP]

 

[Guest]

The total was created in a query using the fields and having them totaled.
But it didn't work when I tried to add the credits used. So I am thinking on
have two subforms from one table. One in datasheet view that shows the
courses taken. One in form view with has the credits summed
(=(sum([credits])), minus the credits used. Then it will be linked to a
person. Before, the unbound part was putting the information in all of the
employees.

Does that sound right to you?

 

[John W. Vinson]

The total was created in a query using the fields and having them totaled.
But it didn't work when I tried to add the credits used. So I am thinking on
have two subforms from one table. One in datasheet view that shows the
courses taken. One in form view with has the credits summed
(=(sum([credits])), minus the credits used. Then it will be linked to a
person. Before, the unbound part was putting the information in all of the
employees.

Does that sound right to you?

It should work. You don't really need a form if its only purpose is to
calculate a sum, though - you can use the DSum() function to sum up the values
in a table, with a criterion to sum them only for the current employee.

John W. Vinson [MVP]