Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Doing caculations with fields in different tables. (Access)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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.

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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%

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Doing caculations with fields in different tables. (Access)

    <<answers + table3.total = table3.total>>

    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.
    Wendell

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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

  8. #8
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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.
    Attached Images Attached Images
    • File Type: png x.png (5.2 KB, 0 views)

  10. #10
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing caculations with fields in different tables. (Access)

    <P ID="edit" class=small>(Edited by WendellB on 01-Apr-03 05:42. activate linke to web page)</P>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

  11. #11
    New Lounger
    Join Date
    Feb 2003
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing caculations with fields in different tables. (Access)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •