I need to bow down to someones infinite wisdom and help me to fix an error when trying to nest this query (i think access refers it as a sub query) below, instead of refering to just the query name
NOT NESTED BUT WORKING
SELECT p.Service_No, p.Lastname, r.Rank, t.total/(Date()-p.TOS) AS ratio
FROM tbl_personnel AS p, tbl_rank AS r, Q_Total_duties_done_list AS t
WHERE (((r.Rank_ID)=p.rank) And ((t.Service_No)=p.service_no))
ORDER BY t.total/(Date()-p.TOS);
QURRY TO NEST -Q_Total_duties_done_list
SELECT p.Service_No, p.Rank, p.Lastname, Count(a.Dates) AS Total
FROM Tbl_Personnel AS p LEFT JOIN Tbl_Gd_Allocation AS a ON p.Service_No=a.Service_No
GROUP BY p.Service_No, p.Rank, p.Lastname;
I have tried putting in this format
SELECT p.Service_No, p.Lastname, r.Rank, t.total/(Date()-p.TOS) AS ratio
FROM tbl_personnel AS p, tbl_rank AS r,
[SELECT p.Service_No, p.Rank, p.Lastname, Count(a.Dates) AS Total
FROM Tbl_Personnel AS p LEFT JOIN Tbl_Gd_Allocation AS a ON p.Service_No=a.Service_No
GROUP BY p.Service_No, p.Rank, p.Lastname;] AS t
WHERE (((r.Rank_ID)=p.rank) And ((t.Service_No)=p.service_no))
ORDER BY t.total/(Date()-p.TOS);
I just get an invalid bracketing of name error really confused why doesnt like the query written in full but works if just refering to the query name. if database required please ask, any information greatly appreciated thanks