General discussion

  • Creator
    Topic
  • #2082433

    how to update tables to zero-not all fie

    Locked

    by boshanek ·

    How do I update a database I created for a school which records fees paid and some refunded fees? This a year end closing and carry forward to the next year.Database layout: primary table has student ID (common field throughout tables),& name.
    Twosecondary tables: one records fees paid with studentID, fee1, fee2, fee3. Second secondary table records the refunded fee1(which is a deposit given back at yearend or carried forward to next year.
    I need to zero out fees except where the refund isnot given. I am not very familar with Visual basic so if this is the direction I would need very clear explanations. This was cut off before 2nd time submitting. Thanks for what-ever help available. Tried update query-came up with errors-I need to update query with primary and both secondary tables because if there is a value in the refunded table it shouldn’t be zeroed.

All Comments

  • Author
    Replies
    • #3892424

      how to update tables to zero-not all fie

      by larry h. ·

      In reply to how to update tables to zero-not all fie

      I’m not 100% sure I understand what you’re trying to do, but here is what I think you can do:

      Create a query that links the two secondary tables, including all fields. In a blank column on the grid create a new calculated field that gives the difference between the fee and the refund. For example, if the fee field is called FEE1 and the refund field is called REFUND1 you would type the following in the first row of the column:
      NEWFEE1 : [FEE1] – [REFUND1]

      If that gives you what you’re looking for either change this query to a MAKE TABLE query to create the basis for the new table (and edit out the extra information you don’t need), or you can use this formula as the basis of an update query.

      Good Luck.

      • #3894430

        how to update tables to zero-not all fie

        by boshanek ·

        In reply to how to update tables to zero-not all fie

        Sorry for the delay larry-out for the week.
        Have gained a solution from experts exchange site, but have used your suggestion is taken under consideration for modifying database in a different direction.
        Thanks for the help!

    • #3892373

      how to update tables to zero-not all fie

      by dmelton ·

      In reply to how to update tables to zero-not all fie

      Larry has you on the right track. However, in order to use the update feature with multiple tables, you will need to have cascade update & delete implemented in your relationships.

Viewing 1 reply thread