General discussion

Locked

how to update tables to zero-not all fie

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.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

how to update tables to zero-not all fie

by Larry H. In reply to how to update tables to z ...

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.

Collapse -

how to update tables to zero-not all fie

by boshanek In reply to how to update tables to z ...

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!

Collapse -

how to update tables to zero-not all fie

by dmelton In reply to how to update tables to z ...

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.

Collapse -

how to update tables to zero-not all fie

by boshanek In reply to how to update tables to z ...

Theses features are on-thanks!

Back to Software Forum
4 total posts (Page 1 of 1)  

Software Forums