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