I have a two table which stores data of main assembly and part numbers respectively and having 1:M relationship. All the original part numbers are serially associated with the main assembly. If one part of the main assembly is swapped with new one. how to create a query to get all the parts associated with the main assembly.
Further i have include a date and fitment status in the part number table also to maintain a history of changed records.
Moreover i also attached an image pertaining to the output of the query. In the attachment main assambly (1001) is related to various part num fitted 10 jan 15. if a worksman change the partnum 51290 (fitted on 10-1-15 and removed on 15-2-17) with 51297 (fitted on 15-2-17). How the query will display all the associated records of mainassm (1001) without REMOVED part.
Thank you.