Optimize a bunch of nested SELECTs in a MS ACCESS query

Question

This is a MS Access query that runs very slow. The Results table is the main table and the P, B, S and C tables are child tables. The column combination Index and ResultID is unique in all of these tables. So for instance, there can only be one row in the table C where Index is "1" and ResultID is 2. I'm not using JOIN because MS Access only support inner joins and what I'd need is an outer join because I want to show all records in the table Results even if there is no corresponding record in any other table.

SELECT 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL],
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "0") as P0,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "1") as P1,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "2") as P2,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "3") as P3,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "4") as P4,
    (SELECT P.P FROM P WHERE P.ResultID = Results.Id AND P.Index = "5") as P5,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "0") as B0,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "1") as B1,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "2") as B2,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "3") as B3,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "4") as B4,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "5") as B5,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "6") as B6,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "7") as B7,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "8") as B8,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "9") as B9,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "10") as B10,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "11") as B11,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "12") as B12,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "13") as B13,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "14") as B14,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "15") as B15,
    (SELECT B.B FROM B WHERE B.ResultID = Results.Id AND B.Index = "16") as B16,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "1") as S1,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "2") as S2,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "3") as S3,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "4") as S4,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "5") as S5,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "6") as S6,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "7") as S7,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "8") as S8,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "9") as S9,
    (SELECT S.S FROM S WHERE S.ResultID = Results.Id AND S.Index = "10") as S10,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "0") as C0,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "1") as C1,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "2") as C2,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "3") as C3,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "4") as C4,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "5") as C5,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "6") as C6,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "7") as C7,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "8") as C8,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "9") as C9,
    (SELECT C.C FROM C WHERE C.ResultID = Results.Id AND C.Index = "10") as C10
FROM 
    Results

Answer 1

I am not sure where you got the following:

I'm not using JOIN because MS Access only support inner joins and what I'd need is an outer join

MS Access supports OUTER JOIN syntax.

You can replace your correlated subqueries with a LEFT JOIN between each table. Your code will be similar to this:

SELECT 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL],
    max(IIF(P.Index = "0", P.P, null)) as P0,
    max(IIF(P.Index = "1", P.P, null)) as P1,
    max(IIF(P.Index = "2", P.P, null)) as P2,
    max(IIF(P.Index = "3", P.P, null)) as P3,
    max(IIF(P.Index = "4", P.P, null)) as P4,
    max(IIF(P.Index = "5", P.P, null)) as P5,
    max(IIF(B.Index = "0", B.B, null)) as B0,
    max(IIF(B.Index = "1", B.B, null)) as B1,
    max(IIF(B.Index = "8", B.B, null)) as B8,
    max(IIF(S.Index = "2", S.S, null)) as S2,
    max(IIF(C.Index = "1", C.C, null)) as C1
FROM Results
LEFT JOIN P
    on P.ResultID = Results.Id
LEFT JOIN B
    on B.ResultID = Results.Id
LEFT JOIN S
    on S.ResultID = Results.Id
LEFT JOIN C
    on C.ResultID = Results.Id
GROUP BY [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL]

You should also be able to pivot the data using the TRANSFORM function, the code will be similar to this:

TRANSFORM Max(Src.Value)
select 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL]
from results as R
left join
(
    select ResultID, P as Value, "P"&Index as col
    from p
    union all
    select ResultID, B as Value, "B"&Index as col
    from B
    union all
    select ResultID, C as Value, "C"&Index as col
    from C
    union all
    select ResultID, S as Value, "S"&Index as col
    from S
) as Src
    on R.id = Src.ResultID
GROUP BY [Results].[Id], [Results].[Name],
    [Results].[OfferPrice],[Results].[RegPrice],
    [Results].[ImageFileName], [Results].[Image],
    [Results].[URL]
PIVOT Src.col

Answer 2

This should run pretty quickly. It looks like you are trying to do a pivot on the join between one table and the union of several tables. The unionized table below replaces all your joins. And the fact that this is pivoted replaces the need to do any correlated sub queries.

TRANSFORM Max(unionized.DataColumn) AS MaxOfDataColumn
SELECT Results.ResName, Results.OfferPrice, Results.RegPrice, Results.Image, Results.URL
FROM (
    Select B.ID, B.B as DataColumn, "B"&B.B as tableName from B
    union all select C.ID, C.C, "C"&C.C from C
    union all select P.ID, P.P, "P"&P.P from P
    union all select S.ID, S.S, "S"&S.S from S)  AS unionized 
RIGHT JOIN Results ON unionized.ID = Results.ID
GROUP BY Results.ResName, Results.OfferPrice, Results.RegPrice, Results.Image, Results.URL
PIVOT unionized.tableName;

You'll need to clean it up a bit because i assume you were using example columns but hopefully you get the idea.

Answer 3

Your query will run faster if you make the joins explicit. Here is the template:

SELECT 
    [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL],
    max(iif(p.index = "0", p.p, "")) as p0,
    . . .
FROM results left outer join
     p
     on results.id = p.resultid left outer join
     . . .
group by [Results].[Id],
    [Results].[Name],
    [Results].[OfferPrice],
    [Results].[RegPrice],
    [Results].[ImageFileName],
    [Results].[Image],
    [Results].[URL]

The idea is to use left outer join to bring in the tables and join them on the correct conditions and use aggregation to shrink them back to one row per result. The logic in the SELECT chooses the values according to the logic you have set up.