Doing caculations with fields in different tables. (Access)

Topic

New Reply

How would you do that?

i got a field in table1 and a field in table2 and i want to divided them and store the answers in a field in table 3. and this needs to be done everytime they enter a value into the form for table 1 field. any advice would be greatly apperciated.

Reply | Quote

Replies

You’re going to get my standard calculated data rant here…

In general, there is no need to store calculated information in a table.

In the first place, it is redundant, derived information. Ordinarily, you’d create a query based on Table1 and Table2 with a calculated field. The calculated field will be updated each time you open the form (and each time you modify the numerator or denominator)

In the second place, it’s hard to ensure that you catch *all* ways the user can enter or modify the field in Table1 and in Table2, so it’s easy for the field in Table3 to get out of synch with the fields in Table1 and Table2. Access doesn’t have table-level triggers; the only way to handle this would be in form-level and/or control-level events (for controls on a form).

If you are sure you need to store the result in Table3, please describe the relationship (if any) between Table1 and Table2.

Reply | Quote

im 100% sure i need it stored unless there is another way to do it which im not thinking of. Basicly its like this.

table2 has a one to many to table1 and table 1 has one to many to table3
(table1.value / table2.totalvalue) * table2.PercentOfTotalValue = answer
then
answers + table3.total = table3.total

basically there is a bunch of values added up to make up to 100%

Reply | Quote

<>

This is an unusual formula – if it’s an assignment, shouldn’t it be reversed so you are updatating table3.

But Hans question still holds – why carry a calculated value that you have to update, and what if someone changes one of the existing values in table 2. The usual answer to this sort of thing is to always calculate the current total of all the values. By storing the value you are creating all sorts of complications.

Reply | Quote

table3 will be holding a value that keeps getting added to.

im thinking i gota throw some vb in here somewhere. and no its not an assignment, its a project. actually ill go more into detail about it.

its a student database which lets teachers add student grades for certain assignments, etc

basically the table1 they enter a student and score on the assignment they got and a assignment “code”. This code is stored in table 2 which holds how much the assignment is worth and also how much the assignment was out of. Thats where the caculation comes in. I gota divide the grade they got on the assignment by the total value of the assignment then multiple it by the percent the assignment is worth. Once thats done it gets ADDED onto the field in table 3 which holds the total grade for a student in a certain class. so basically teh students start at 0 and work there way up to 100.

Reply | Quote

Would you just give us a brief rundown on your tables and their contents.
Specify the 1-M type relationships and the fields that form the relationship between them.
eg.
Table1 (one) to Table3 (many)
Table2 (one) to Table1 (many)
Is my example correct?

Pat

Reply | Quote

(Edited by WendellB on 01-Apr-03 05:42. activate linke to web page)

i drew up a quick picture of it. go to 1 then to 2 then to 3 to understand it a bit better.

http://24.78.165.11/pic/accessrelationship.JPG%5B/url%5D

Reply | Quote

From what you have described the relationship between T1 and T3 should be 1 (T3) to many (T1).
How can you update a field (Grade in T3) when you describe T3 as a many side of T1.
Am I missing something here?

If it is as I assume then you can work this out via a query. What happens if Value in T1 is changed, how then do you accurately keep the total (Grade) in T3? You would have to run an update query for all cases.

Pat

Reply | Quote

ya i got update query now for it which kinda makes it lame. I think i gota think of another way to do this. and t1 isnt one to many t3.

Reply | Quote

You can use a Totals query to add the weighted scores. No need for VBA.

Here is the SQL for a simplified version (I left out Year, Term, Course and Section):

SELECT tblStudentGradeUnit.StudentID, Sum(([Value]/[MarkingValue])*([PercentOfTotalMark]/100)) AS Grade
FROM tblMarkingUnit INNER JOIN tblStudentGradeUnit ON tblMarkingUnit.UnitNumber = tblStudentGradeUnit.UnitNumber
GROUP BY tblStudentGradeUnit.StudentID;

I have attached a screenshot of the query in design view. Captions are in Dutch, but the general idea will be clear, I hope.

Reply | Quote

sweet thanks, that basically worked. had to do some rigging to get it to update to the tblgrade (kept saying must use updateable query so i appened it to a new table called tablegetgrade then i just used that table to udpate tblgrade, worked fine sorta)

thanks for help everyone, that worked good.

Reply | Quote