Code:
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, FRT_Import_Table.[POL Name], FRT_Import_Table.[POD Name], FRT_Import_Table.Carrier, FRT_Import_Table.[Contract Type], FRT_Import_Table.Contract, FRT_Import_Table.[20GP Cost], FRT_Import_Table.[40GP Cost], FRT_Import_Table.[40HC Cost], FRT_Import_Table.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])=[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])=[FRT_Table].[Valid To]))
UNION
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, FRT_Import_Table.[POL Name], FRT_Import_Table.[POD Name], FRT_Import_Table.Carrier, FRT_Import_Table.[Contract Type], FRT_Import_Table.Contract, FRT_Import_Table.[20GP Cost], FRT_Import_Table.[40GP Cost], FRT_Import_Table.[40HC Cost], FRT_Import_Table.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])>[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, 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.Notes, [FRT_Import_Table].[Valid To]+1 AS [Valid From], FRT_Table.[Valid To] AS [Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name]) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type])
WHERE (((FRT_Import_Table.[Valid From])>[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION
SELECT "U" AS [Action], FRT_Table.ID AS SRCID, 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.Notes, FRT_Table.[Valid From], [FRT_Import_Table].[Valid From]-1 AS [Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name]) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type])
WHERE (((FRT_Import_Table.[Valid From])>[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, 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.Notes, [FRT_Import_Table].[Valid To]+1 AS [Valid From], FRT_Table.[Valid To] AS [Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])=[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION
SELECT "U" AS [Action], FRT_Table.ID AS SRCID, FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Import_Table.Contract, FRT_Import_Table.[20GP Cost], FRT_Import_Table.[40GP Cost], FRT_Import_Table.[40HC Cost], FRT_Import_Table.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name]) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type])
WHERE (((FRT_Import_Table.[Valid From])=[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, FRT_Import_Table.[POL Name], FRT_Import_Table.[POD Name], FRT_Import_Table.Carrier, FRT_Import_Table.[Contract Type], FRT_Import_Table.Contract, FRT_Import_Table.[20GP Cost], FRT_Import_Table.[40GP Cost], FRT_Import_Table.[40HC Cost], FRT_Import_Table.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.Contract = FRT_Import_Table.Contract) AND (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])>[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])=[FRT_Table].[Valid To]))
UNION
SELECT "U" AS [Action], FRT_Table.ID AS SRCID, 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.Notes, FRT_Table.[Valid From], [FRT_Import_Table].[Valid From]-1 AS [Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name]) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Contract = FRT_Import_Table.Contract)
WHERE (((FRT_Import_Table.[Valid From])>[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])=[FRT_Table].[Valid To]))
UNION
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, FRT_Import_Table.[POL Name], FRT_Import_Table.[POD Name], FRT_Import_Table.Carrier, FRT_Import_Table.[Contract Type], FRT_Import_Table.Contract, FRT_Import_Table.[20GP Cost], FRT_Import_Table.[40GP Cost], FRT_Import_Table.[40HC Cost], FRT_Import_Table.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From]) Between [FRT_Table].[Valid From] And [FRT_Table].[Valid To]) AND ((FRT_Import_Table.[Valid To])>[FRT_Table].[Valid To]))
UNION
SELECT "U" AS [Action], FRT_Table.ID AS SRCID, 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.Notes, FRT_Table.[Valid From], [FRT_Import_Table].[Valid From]-1 AS [Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From]) Between [FRT_Table].[Valid From] And [FRT_Table].[Valid To]) AND ((FRT_Import_Table.[Valid To])>[FRT_Table].[Valid To]))
UNION
SELECT "I" AS [Action], FRT_Import_Table.ID AS SRCID, FRT_Import_Table.[POL Name], FRT_Import_Table.[POD Name], FRT_Import_Table.Carrier, FRT_Import_Table.[Contract Type], FRT_Import_Table.Contract, FRT_Import_Table.[20GP Cost], FRT_Import_Table.[40GP Cost], FRT_Import_Table.[40HC Cost], FRT_Import_Table.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])<[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION
SELECT "U" AS [Action], FRT_Table.ID AS SRCID, 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.Notes, [FRT_Import_Table].[Valid To]+1 AS [Valid From], FRT_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])<[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])<[FRT_Table].[Valid To]))
UNION SELECT "U" AS [Action], FRT_Table.ID AS SRCID, 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.Notes, FRT_Import_Table.[Valid From], FRT_Import_Table.[Valid To]
FROM FRT_Table INNER JOIN FRT_Import_Table ON (FRT_Table.[Contract Type] = FRT_Import_Table.[Contract Type]) AND (FRT_Table.Carrier = FRT_Import_Table.Carrier) AND (FRT_Table.[POD Name] = FRT_Import_Table.[POD Name]) AND (FRT_Table.[POL Name] = FRT_Import_Table.[POL Name])
WHERE (((FRT_Import_Table.[Valid From])<=[FRT_Table].[Valid From]) AND ((FRT_Import_Table.[Valid To])>[FRT_Table].[Valid To]))
ORDER BY [POL Name], [POD Name], Carrier, [Contract Type], [Valid From];