Thank you all for your help. A friend of mine came up with the query below to get the desired table.
Below the query I attached a pic of one of the resulting tables.
Thanks again.
SELECT q1.StartTime AS "Start Time",
q1.ProductCode as "Product Code",
q1.Lot AS "Lot",
q1.FV AS "FV",
q1.Volume AS "Volume",
@totVol := @totVol + q1.Volume AS "Balance",
q1.Source AS "Source"
FROM
(SELECT
StartTime AS "StartTime", Name AS "ProductCode", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume", "Whirl Pool" AS "Source"
FROM kyla_ko AS q2, (SELECT @totVol := 0) AS d
WHERE fv = 3 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "StartTime", name AS "ProductCode", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume", "Whirl Pool" AS "Source"
FROM wp_ko AS q3
WHERE fv = 3 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "StartTime", srcName AS "ProductCode", srcLot AS "Lot", dest AS "FV", TotVol AS "Volume", CONCAT ('Rack From FV ',source) AS "Source"
FROM rack AS q4
WHERE dest = 3 AND destname = "{Root Container.Product Code Lbl.text}" AND destlot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "StartTime", srcName AS "ProductCode", srcLot AS "Lot", Source AS "FV", TotVol * -1 AS "Volume", CONCAT('Rack To FV ', dest) AS "Source"
FROM rack as q5
WHERE Source = 3 AND srcName = "{Root Container.Product Code Lbl.text}" AND srcLot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "StartTime", Name AS "ProductCode", Lot AS "Lot", fv AS "FV", Total_Bls * -1 AS "Volume", "Spin" AS "Source"
FROM bt_runin as q6
WHERE fv = 3 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "StartTime", name AS "ProductCode", lot AS "Lot", "3" AS "FV", vol/31 AS "Volume", CONCAT('Diverted From knockout ', name," ", lot) AS "Source"
FROM divert as q7
WHERE fv = 3 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT t_stamp AS "StartTime", wortBrew AS "ProductCode", wortBatch AS "Lot", wortFV AS "FV", ((ActualPitch)/9.04)/31 AS "Volume", CONCAT('Yeast Pitch ',lablot) AS "Source"
FROM harvest as q8
WHERE wortfv = 3 AND wortBrew = "{Root Container.Product Code Lbl.text}" AND wortBatch LIKE "{Root Container.Label 74.text}%"
UNION
SELECT t_stamp AS "StartTime", srce1Brew AS "ProductCode", srce1Batch AS "Lot", srce1FV AS "FV", (((srce1wt)/9.04)/31)*-1 AS "Volume", CONCAT('Yeast Harvest ',srce1Lot) AS "Source"
FROM harvest as q9
WHERE srce1FV = 3 AND srce1Brew = "{Root Container.Product Code Lbl.text}" AND srce1Batch LIKE "{Root Container.Label 74.text}%"
UNION
SELECT t_stamp AS "StartTime", srce2Brew AS "ProductCode", srce2Batch AS "Lot", srce2FV AS "FV", (((srce2wt)/9.04)/31)*-1 AS "Volume", CONCAT('Yeast Harvest ',srce2Lot) AS "Source"
FROM harvest as q10
WHERE srce1FV = 3 AND srce1Brew = "{Root Container.Product Code Lbl.text}" AND srce1Batch LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "StartTime", name AS "ProductCode", lot AS "Lot", source AS "FV", (vol / 31)*-1 AS "Volume", type AS "Source"
FROM dump as q11
WHERE source = 3 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
) as q1
ORDER BY q1.StartTime ASC
