CODEWITHSUNDEEP
databasems-access
MirrorMirror
MirrorMirror

Reputation: 188

ms access query hangs

qFieldInner

PARAMETERS pText1 Text ( 255 );
SELECT entries.en_main, MIN(entries.en_id) AS en_id
FROM entries
WHERE (((entries.en_main) In (625, 1310)))
GROUP BY en_main;

when i run this query it returns:

625 76719
1310 177239

however if I transform it to this:

PARAMETERS pText1 Text ( 255 );
SELECT entries.en_main, MIN(entries.en_id) AS en_id
FROM entries
WHERE (((entries.en_main) In (SELECT en_main FROM qFieldInnest)))
GROUP BY en_main;

then it just hangs.

qFieldInnest is this and correctly returns 625 and 1310 when i run it manually

PARAMETERS pText1 Text ( 255 );
SELECT DISTINCT entries.en_main
FROM entries
WHERE (((entries.en_lect) Like ("* " & [pText1] & "*") Or (entries.en_lect) Like ([pText1] & "*")) AND ((entries.en_ref)=0) AND (((entries.en_se)=1) OR ((entries.en_meros)=6))) OR (((entries.en_thama) Like ("* " & [pText1] & "*") Or (entries.en_thama) Like ([pText1] & "*")));

I can't understand what is going wrong here.

Upvotes: 1

Views: 1992

Answers (1)

HansUp
HansUp

Reputation: 97101

I would approach this with a query which uses the SQL from qFieldInnest as a subquery, and INNER JOIN the subquery to the entries table.

The WHERE clause in qFieldInnest is challenging for me to follow. It will challenge the db engine, too, because of the amount of work it has to do.

I have nothing to test against, so uncertain whether I made mistakes in the following SQL. If it doesn't work, break out the subquery SQL into a new query and test that separately.

PARAMETERS pText1 Text ( 255 );
SELECT e.en_main, MIN(e.en_id) AS en_id
FROM
    entries AS e
    INNER JOIN
    (
        SELECT DISTINCT en_main
        FROM entries
        WHERE
                (
                    (
                           en_lect Like "* " & [pText1] & "*"
                        Or en_lect Like [pText1] & "*"
                    )
                    AND en_ref=0
                    AND
                        (
                               en_se=1
                            OR en_meros=6
                        )
                )
            OR
                (
                       en_thama Like "* " & [pText1] & "*"
                    Or en_thama Like [pText1] & "*"
                )

    ) AS sub
    ON e.en_main = sub.en_main
GROUP BY e.en_main;

Upvotes: 3

Related Questions