Holy table mess Batman
I would guess from all the repeated fields (Brand, Legacy Number, FV# etc) in those tables your data is not normalised, and as a result your queries are turning into unfathomable mushes.
I don't understand the use of Batch number in every table, if that data really is always related it belongs in the same table.
You should have a master table for the batch, that has all the unique things for that batch in it.
Any data that can have multiple related versions of an entity should be in another table with the Batch number as the Foreign Key, not the primary key.