few complications with a stock database
i'm creating a stock database for my company.
i have my main layouts: aircraft, parts and work orders. i can create a work order for specific aircraft and also add used parts on it.
i have come across with few complications:
1) for example on one day there comes in a set of 200 screws by a part number 555 and i give it a batch number like 12345 when adding them to stock. on a second day there comes a new set of 300 screws with the same part number and i give them a batch number 54321. now there are two records with different batch numbers but with same part numbers. how can i make a list of parts where the parts with same part numbers are shown as one record and their quantities have been sum'd up? so i could have a perfect up-to-date overview of the parts and their quantities that i have in my stock.
2) aircraft. this is where all the information about a certain aircraft is held – registration number, serial number, type, year, engine, propeller, etc. for example if i make a work order for aircraft X, it is a usual 50 flight hour inspection. all the same information about the aircraft appears on the work order. this is good. but what if something happens to the aircraft and the engine needs to be replaced. well i create new work order about engine replacement. but now information about the aircraft changes – new engine with new PN and SN. I can change this info via work order or aircraft, no problem. but now the problem comes up with the first work order – the information about the engine has updated there also and i don't want that to happen because when that work order was made it still had the old engine. so how can i manage to keep the information in old work orders unchanged?
3) this thing i don't know if it's possible but i'm going to ask anyway. let's say i finished up with the work order. the work order displays info about the aircraft, work needed to be done, work done, list of used parts with pdf files of their certificates in their container fields. is it possible to write a script that takes this work order and all the certificates and adds them together as a single pdf file and save it?
if i can manage at least with the first two problems it would be great.