Info not in table or query

[alguzman]

I have a main form with a payments form attached by member number and a order form also joined by member number. The payments form has amount of product, deposit, payment1 payment2 and amount due. All these feilds are in the payments table. On the payment form in the control "amount due" I have the following formula =[amount of product]-[deposit]-[payment1]-[payment2]. It works on the form and shows amount due if someone did not pay in full.

Problem is when I query out using the tables CUSTOMERS, PAYMENTS and PRODUCTS (all joined by one to many relationship the one side is the Customers table and many being payments and products). I put the feilds companyname (from CUSTOMERS table), Products (from PRODUCTS table) and Amount Due (from PAYMENTS table). when I veiw the query I do not see the amount due, but I see it in the form. When I opned the Payments table I do not see the amount due there either. I tried to fix to no end. Can someone please advise and help me out here. Thanks.

 

[David R]

The reason you're not seeing it is because it's not being stored. You've created a "calculated field" and general database structure states that it is usually poor design to store calculations.

There are two main reasons for this: 1) It effectively stores the data 'twice', since you have all the pieces of the equation stored elsewhere; 2) Depending on how you force the table to store the calculation, you can create errors if any of the underlying numbers are changed or corrected at a later date.

A better solution, which will still let you do what you want (access the Amount Due field from other objects) is to make a query based on the Payments table, and include the calculated field Amount Due: =[amount of product]-[deposit]-[payment1]-[payment2]. Then use this query as the source for your form, report, and other queries which need this field. You can also delete the (blank) Amount Due) field from your table, saving yourself a bit of space.

HTH,
David R

 

[David R]

*smacks forehead for not seeing this before, but it IS ungodly early*

I noticed you have two fields "Payment1" and "Payment2" in your Payments table. Have you looked at any of the resources on Database Normalization? I have a feeling you might have two fields here where you only need one, but I'd need to know more about your database to be sure.

Consider this scenario: What if a person needs to make three payments for one order? Are you going to have to make a whole new field? If I'm misunderstanding your database structure, then disregard this, but Normalization is a good thing to read up on in any case.

David R

 

[alguzman]

Thanks David R. I will look into your first suggestion. Thanks again.