Nested Queries

J

John Ortt

Hi All,

I have a query which runs off two sub-queries.I have tried to include them
as nested queries in the original but it hasn't worked.

Below is my revised query followed by the original and then the two sub
queries.

If anyone can see any obvious mistakes I would be grateful.

Thanks,

John

MY QUERY
----------------
SELECT tblStores.idxStore, tblStores.strStore, Query13.CountOfidxStkIdent AS
StoreTotal,
nz([CountOflngStkIdent])+0 AS StoreCounted,
nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]) AS Remainder
FROM (tblStores LEFT JOIN (SELECT tblStores.idxStore,
Count(tblPartNos.idxStkIdent) AS CountOfidxStkIdent
FROM tblStores INNER JOIN tblPartNos ON tblStores.idxStore =
tblPartNos.lngStore
GROUP BY tblStores.idxStore) as Query13 ON tblStores.idxStore =
Query13.idxStore)
LEFT JOIN (SELECT tblStkChk.lngStore, Count(tblStkChkData.lngStkIdent) AS
CountOflngStkIdent
FROM tblStkChk INNER JOIN tblStkChkData ON tblStkChk.idxStkCheck =
tblStkChkData.lngStkCheck
WHERE (((tblStkChkData.intStrChk) Is Not Null) AND
((tblStkChkData.intSysChk) Is Not Null))
GROUP BY tblStkChk.lngStore) as Query14 ON tblStores.idxStore =
Query14.lngStore
WHERE (((nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]))<>0));

ORIGINAL
---------------
SELECT tblStores.idxStore, tblStores.strStore, Query13.CountOfidxStkIdent AS
StoreTotal,
nz([CountOflngStkIdent])+0 AS StoreCounted,
nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]) AS Remainder
FROM (tblStores LEFT JOIN Query13 ON tblStores.idxStore = Query13.idxStore)
LEFT JOIN Query14 ON tblStores.idxStore = Query14.lngStore
WHERE (((nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]))<>0));

QUERY 13
---------------
SELECT tblStores.idxStore, Count(tblPartNos.idxStkIdent) AS
CountOfidxStkIdent
FROM tblStores INNER JOIN tblPartNos ON tblStores.idxStore =
tblPartNos.lngStore
GROUP BY tblStores.idxStore;

QUERY 14
---------------
SELECT tblStkChk.lngStore, Count(tblStkChkData.lngStkIdent) AS
CountOflngStkIdent
FROM tblStkChk INNER JOIN tblStkChkData ON tblStkChk.idxStkCheck =
tblStkChkData.lngStkCheck
WHERE (((tblStkChkData.intStrChk) Is Not Null) AND
((tblStkChkData.intSysChk) Is Not Null))
GROUP BY tblStkChk.lngStore;
 
G

Gary Walter

Hi John,

One thing that stands out to me is that you might want to
give tblStores an alias in first subquery.

SELECT
tblStores.idxStore,
tblStores.strStore,
Query13.CountOfidxStkIdent AS StoreTotal,
nz([CountOflngStkIdent])+0 AS StoreCounted,
nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]) AS Remainder
FROM

(tblStores

LEFT JOIN

(SELECT
S.idxStore,
Count(P.idxStkIdent) AS CountOfidxStkIdent
FROM
tblStores As S
INNER JOIN
tblPartNos As P
ON
S.idxStore = P.lngStore
GROUP BY
S.idxStore) as Query13

ON
tblStores.idxStore = Query13.idxStore)

LEFT JOIN

(SELECT
tblStkChk.lngStore,
Count(tblStkChkData.lngStkIdent) AS CountOflngStkIdent
FROM
tblStkChk
INNER JOIN
tblStkChkData
ON tblStkChk.idxStkCheck = tblStkChkData.lngStkCheck
WHERE (
((tblStkChkData.intStrChk) Is Not Null)
AND
((tblStkChkData.intSysChk) Is Not Null)
)
GROUP BY
tblStkChk.lngStore) as Query14

ON
tblStores.idxStore = Query14.lngStore
WHERE (
((nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]))<>0)
);

John Ortt said:
I have a query which runs off two sub-queries.I have tried to include them
as nested queries in the original but it hasn't worked.

Below is my revised query followed by the original and then the two sub
queries.

If anyone can see any obvious mistakes I would be grateful.

Thanks,

John

MY QUERY
----------------
SELECT tblStores.idxStore, tblStores.strStore, Query13.CountOfidxStkIdent
AS StoreTotal,
nz([CountOflngStkIdent])+0 AS StoreCounted,
nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]) AS Remainder
FROM (tblStores LEFT JOIN (SELECT tblStores.idxStore,
Count(tblPartNos.idxStkIdent) AS CountOfidxStkIdent
FROM tblStores INNER JOIN tblPartNos ON tblStores.idxStore =
tblPartNos.lngStore
GROUP BY tblStores.idxStore) as Query13 ON tblStores.idxStore =
Query13.idxStore)
LEFT JOIN (SELECT tblStkChk.lngStore, Count(tblStkChkData.lngStkIdent) AS
CountOflngStkIdent
FROM tblStkChk INNER JOIN tblStkChkData ON tblStkChk.idxStkCheck =
tblStkChkData.lngStkCheck
WHERE (((tblStkChkData.intStrChk) Is Not Null) AND
((tblStkChkData.intSysChk) Is Not Null))
GROUP BY tblStkChk.lngStore) as Query14 ON tblStores.idxStore =
Query14.lngStore
WHERE (((nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]))<>0));

ORIGINAL
---------------
SELECT tblStores.idxStore, tblStores.strStore, Query13.CountOfidxStkIdent
AS StoreTotal,
nz([CountOflngStkIdent])+0 AS StoreCounted,
nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]) AS Remainder
FROM (tblStores LEFT JOIN Query13 ON tblStores.idxStore =
Query13.idxStore)
LEFT JOIN Query14 ON tblStores.idxStore = Query14.lngStore
WHERE (((nz([CountOfidxStkIdent])-nz([CountOflngStkIdent]))<>0));

QUERY 13
---------------
SELECT tblStores.idxStore, Count(tblPartNos.idxStkIdent) AS
CountOfidxStkIdent
FROM tblStores INNER JOIN tblPartNos ON tblStores.idxStore =
tblPartNos.lngStore
GROUP BY tblStores.idxStore;

QUERY 14
---------------
SELECT tblStkChk.lngStore, Count(tblStkChkData.lngStkIdent) AS
CountOflngStkIdent
FROM tblStkChk INNER JOIN tblStkChkData ON tblStkChk.idxStkCheck =
tblStkChkData.lngStkCheck
WHERE (((tblStkChkData.intStrChk) Is Not Null) AND
((tblStkChkData.intSysChk) Is Not Null))
GROUP BY tblStkChk.lngStore;
 
J

John Ortt

Gary Walter said:
Hi John,

One thing that stands out to me is that you might want to
give tblStores an alias in first subquery.
Thanks Gary,

I'll try your suggestion and let you know how I get on.
 
J

John Ortt

Doh,

It doesn't seem to be helping but I'll keep trying. I must be missing
something.
 
Top