I’ll do the best I can to explain what I would like my program to do, maybe you can give me a little bit more guidance.
Alright – I have a database with the following 3 tables
Assets – Asset ID (PK)
Inventory – no PK
Preventative Maintenance – no PK
I created corresponding forms for each table, each with the same name.
Here is what I can do as of now:
Assets form - I am able to enter the asset #, description, serial #, hours between PM (preventative maintenance), and
starting hours (same as an odometer) which I have set to 0.
Inventory form – Able to enter Part#, Asset ID (this allows the user to distinguish which asset this part was made for), Part
Type, Part Description, Vendor, Location, Quantity, Individual Price, Total Price, Tax (Of the currency fields I just
mentioned, I have it setup calculate the Total Price by multiplying the quantity * individual price. I also set it up to
calculate 7% sales tax within the form)
Preventative Maintenance – The first field is PM# (auto number), Asset ID (distinguish which asset is being worked on), Date
Checked In, Time In, Time Out, Current Hours, PM Performed, Part #, Quantity Used.
Well hopefully I haven’t just thrown a lot of information your way. So now that I have given you what my project can do, let
me try and explain what I want it to do. And please keep in mind that I have been trying every which way I can to create the
proper relationships, or foreign and primary keys, but that concept still passes by me at times.
I want to be able to know that if I have 10 tires for asset 1500, and I perform a PM on Asset 1500 and use 2 tires, that I
will have 8 remaining. Maybe I just am missing the simple solution here, but I have been trying for days. Also, that value
8 should be the new on hand value within the inventory. I need it to update.
If I have 10 tires with part # TIR1500, and then I receive 2 more a few days later, how can I get the quantities to
correspond? It seems as though I was able to work this out through queries to get the correct info, but due to the
relationships I tried setting up, when I got to the PM and the quantity used, it threw my totals off.