Using calculated Fields...A Big Mistake?

[back2basic]

I hope my experience will be of help to some people because now I am in over my head in a job that has taken three times longer then I ever expected.

I was volunteered to create a DB which simply tracks the equipment at our facility and who the equipment is assigned to. I volunteered simply because I have created a few DB's using "basic" in the 90' and figured what the heck this will be simple.....Right! When I learned to code DB's were created from scratch ...each screen, table and form using all basic code and you crunched through the code until you got the results you needed and....calculated fields were very common and probably necessary.

Well I created a DB with my neanderthal mentality using a calculated field and now I have a serious dilemma and to be quite honest I am still not quite sure it is all that wrong or WHAT SHOULD I DO to fix it?

If you are willing and would like to help me please let me know and I will provide more information on my design because I need to go back and write some EXTREMELY intelligent queries to get this right and I am out of time.

 

[back2basic]

Please reference the attached screen shot of the DB's relationships. Needless to say this screen shot shows all tables, table fields and parent child relationships. I don't want to be over bearing with this explanation however I am not quite sure which details will help explain the problem. So, I figure more is better:

The way this DB is designed is that the table "Assigned_Materials" is used to store records each time a material item is checked out by an employee. A form created from the Assigned_Materials table has two Combo Box's ( One for the Materials table and one for the Employees table). I can select a material and select an employee, enter the date and create a record showing an employee has checked out a material. Now this where the mistake was made:

I setup a VBA Sub in the Materials Combo Box which prompts the user to enter the quantity of the material the employee needs. At this point, I subtracted this quantity from the Remaining_Quantity field of the Materials Table and Increased the Issued_Quantity field of the Materials Table. I needed to do this each time the form was opened or each time a record was added to show the user how much material was left so. Also if a material was all checked out or zero to not show it in the Combo Box.

I do realize, I can do this by simply adding a Issued_Quantity field and Returned_Quantity field to the Assigned_Materials table and simply totaling all the records ( of this material type).

You graciously gave me an example line of code to do this: But these query statements are complex and for now, I do not correctly know the syntax for using them.

Eventually the Redundant fields of the Materials Table will be deleted. I understand this.

 

[mdlueck]

Do you mean that your application is live changing the values in the fields, and there is no journal entries - like a check book ledger?

How were you expecting the application to work? (Ignore your existing design / implementation to answer this question.)

 

[back2basic]

Do you mean that your application is live changing the values in the fields, and there is no journal entries - like a check book ledger?

How were you expecting the application to work? (Ignore your existing design / implementation to answer this question.)

Not quite, the Materials table and Employees table are both completely populated and for the most part fixed except when new materials or or new employees are added. The Assigned_Materials Table is populated with records of each employee that checks out a material indicating the Employee, Quantity_Issued, Material type and Date. The Stock_Quantity and Issued_ Quantity also remained fixed.

The problem is instead of running a query of the Assigned_Materials table records to calculate the quantity of a material remaining, I have been keeping a "running total" or calculated amount each time the material was checked out in the Remaining_Quantity field of the Materials table.

 

[plog]

You've got a few things wrong, and a few things that might be wrong.

1. Material_Assignment: Shouldn't have both a date_returned and date_allocated fields, nor both Quantity_issued and Quantity_Returned fields. That table should just have an AssignmentDate field, an AssignmentQuantity field and AssignmentType field which would tell you if it was a return or an allocation.

2. Materials: Shouldn't have Remaining_Quantity field, instead should use the Material_Assignment table to calculate that whenever you need it.

3. Probably shouldn't have a Stock_Quantity field. I assume this holds however many you have of a particular Material. Instead of having this here I would make it a record in Material_Assignment. Most likely I would create a new value for the AssignmentType field I mentioned previous. This field would now say 'Allocation', 'Return' and 'Material Acquisition' which means you added more of that material to that which is available.

4. Material_Location - Materials and Manufactures - Materials: Something doesn't feel right among these relationships. I don't think they are properly normalized. My guess is you need another table. Here's how to find out--if Part_number and Material_Name appear in here multiple times, but always together, then you need another table and create that relationship there.

If you could post sample data I could help you out better on that last one, but I'm guessing the relationship among those 3 tables is not correct.

 

[back2basic]

You've got a few things wrong, and a few things that might be wrong.

1. Material_Assignment: Shouldn't have both a date_returned and date_allocated fields, nor both Quantity_issued and Quantity_Returned fields. That table should just have an AssignmentDate field, an AssignmentQuantity field and AssignmentType field which would tell you if it was a return or an allocation.

This is a good idea, will work and also saves a field however, please keep in mind, I am not using the same form to Assign_Materials as I am to Return_Materials. Therefore I already know if the material is going out or coming back into stock. Now I know you ask why are you doing this you only need one form...agree but I will have to prompt the user if it is assignment or return with one form.

Also, of the materials checked out of the materials table ( which more then one can be checked out), the exact same material must be returned. And to make matters worse, an employee may not return all that he has checked out at once. Therefore, I am using a Combo Box in the Assign_Materials table to locate the record of the material checked out by that employee ( This may not be a good way but makes it easy to ensure I do not make a mistake in returning a material that was not checked out.

2. Materials: Shouldn't have Remaining_Quantity field, instead should use the Material_Assignment table to calculate that whenever you need it.

I agree this is a redundant field and is really the basis of my whole problem. It was necessary when I was calculating the Remaining_Quantity but will be deleted after I get this query working.

3. Probably shouldn't have a Stock_Quantity field. I assume this holds however many you have of a particular Material. Instead of having this here I would make it a record in Material_Assignment. Most likely I would create a new value for the AssignmentType field I mentioned previous. This field would now say 'Allocation', 'Return' and 'Material Acquisition' which means you added more of that material to that which is available.

Okay, I get your point but returned materials should never be more then what was check out by that employee. Also, Stock_Quantity is basically our inventory available to employees and is only updated when new materials are purchased or obsoleted.

4. Material_Location - Materials and Manufactures - Materials: Something doesn't feel right among these relationships. I don't think they are properly normalized. My guess is you need another table. Here's how to find out--if Part_number and Material_Name appear in here multiple times, but always together, then you need another table and create that relationship there.

Please allow me to clarify, Material_Location is simply a table to show where that material is in the yard i.e., which building of 9 is it in. This is simply so I do not have to keep retyping this field. Manufacture is only a list of who makes our materials (Websites) and is really not needed at all for this DB

If you could post sample data I could help you out better on that last one, but I'm guessing the relationship among those 3 tables is not correct.

If you work like me to upload what I have done so you can take a look and try, I would be happy to. Thank you very much for your support.

Dale