Explanation On This Query Code

[stildawn]

Hi All



If at all possible, could someone in the know just have a look at the below Union query code and tell me what each part does and how, I need to fix it but I just dont understand it, I'm willing to learn so just need a few comments on each section so I can research and figure it out:

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];

[ranman256]

put each 'block' into its own query, run it. you may get a better idea of its use.
the union query can then be:

select * from Q1
union
select * from Q2
union
select * from Q3

[ssanfu]

It looks like there are 11 queries being grouped together in one UNION query - the only difference seems to be the letter in the first column "I" or "U" of each query and the different WHERE clauses .

What are you trying to fix??

[stildawn]

It's not doing what I thought it does, I didnt write it so playing catch up and my access sql skills are very lacking.

Was hoping for a brief run down of each section and then I could teach myself off that

[isladogs]

OK I'll try to explain
Each section of the union query is a query in its own right which selects specified data from the selected tables with specified filter conditions.
The sections are then combined using UNION to list all the results for each individual query.
Does that help?

[wvmitchell]

Ar first glance, each section is comparing the two tables and classifying the records as I or U depending on the [Valid From] and [Valid To] dates. Then the (unique rows) sections are combined in the UNIONs.
Does that help?

[stildawn]

Thanks guys, forgot to say thanks, the above is pretty much what I was after and I'm learning them one by one to sort out.

Cheers