Wow, took well over a minute just to load the form.
I broke down your union query MasterData2 and found that this portion of it takes forever to load and my guess is that's your culprit. A further guess is that the triple right join has something to do with it.
I'm not that good with that stuff so hopefully someone can spot it.
Code:
SELECT FRT_Table.ID, FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Table.Contract, FRT_Table.[20GP Cost], FRT_Table.[40GP Cost], FRT_Table.[40HC Cost], FRT_Table.[Valid From], FRT_Table.[Valid To], Transits_Table.Transit, Transits_Table.Direct, FRT_Table.Notes
FROM Transits_Table RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name]) AND (Transits_Table.PODName = FRT_Table.[POD Name]) AND (Transits_Table.Carrier = FRT_Table.Carrier)
WHERE (((FRT_Table.ID) Not In (SELECT Master_Data.ID FROM Master_Data) And (FRT_Table.ID) Not In (SELECT Master_Data_Offset.ID FROM Master_Data_Offset)));
here's an easier version to read. Hopefully someone better with SQL can better spot the problem.
Code:
SELECT FRT_Table.ID
,FRT_Table.[POL Name]
,FRT_Table.[POD Name]
,FRT_Table.Carrier
,FRT_Table.[Contract Type]
,FRT_Table.Contract
,FRT_Table.[20GP Cost]
,FRT_Table.[40GP Cost]
,FRT_Table.[40HC Cost]
,FRT_Table.[Valid From]
,FRT_Table.[Valid To]
,Transits_Table.Transit
,Transits_Table.Direct
,FRT_Table.Notes
FROM Transits_Table
RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name])
AND (Transits_Table.PODName = FRT_Table.[POD Name])
AND (Transits_Table.Carrier = FRT_Table.Carrier)
WHERE (
(
(FRT_Table.ID) NOT IN (
SELECT Master_Data.ID
FROM Master_Data
)
AND (FRT_Table.ID) NOT IN (
SELECT Master_Data_Offset.ID
FROM Master_Data_Offset
)
)
);
Heres the full union query
Code:
SELECT Master_Data.ID
,Master_Data.[POL Name]
,Master_Data.[POD Name]
,Master_Data.Carrier
,Master_Data.[Contract Type]
,Master_Data.Contract
,Master_Data.[20GP All In]
,Master_Data.[40GP All In]
,Master_Data.[40HC All In]
,Master_Data.[Valid From]
,Master_Data.[Valid To]
,Master_Data.Transit
,Master_Data.Direct
,Master_Data.Notes
FROM Master_Data
UNION
SELECT FRT_Table.ID
,FRT_Table.[POL Name]
,FRT_Table.[POD Name]
,FRT_Table.Carrier
,FRT_Table.[Contract Type]
,FRT_Table.Contract
,FRT_Table.[20GP Cost]
,FRT_Table.[40GP Cost]
,FRT_Table.[40HC Cost]
,FRT_Table.[Valid From]
,FRT_Table.[Valid To]
,Transits_Table.Transit
,Transits_Table.Direct
,FRT_Table.Notes
FROM Transits_Table
RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name])
AND (Transits_Table.PODName = FRT_Table.[POD Name])
AND (Transits_Table.Carrier = FRT_Table.Carrier)
WHERE (
(
(FRT_Table.ID) NOT IN (
SELECT Master_Data.ID
FROM Master_Data
)
AND (FRT_Table.ID) NOT IN (
SELECT Master_Data_Offset.ID
FROM Master_Data_Offset
)
)
);
UNION
SELECT Master_Data_Offset.ID
,Master_Data_Offset.[POL Name]
,Master_Data_Offset.[POD Name]
,Master_Data_Offset.Carrier
,Master_Data_Offset.[Contract Type]
,Master_Data_Offset.Contract
,Master_Data_Offset.[20GP All In]
,Master_Data_Offset.[40GP All In]
,Master_Data_Offset.[40HC All In]
,Master_Data_Offset.[Valid From]
,Master_Data_Offset.[Valid To]
,Master_Data_Offset.Transit
,Master_Data_Offset.Direct
,Master_Data_Offset.Notes
FROM Master_Data_Offset;