joining crosstab query with group by query

[masoud_sedighy]

I have 2 queries one of them is crosstab query and second one is group by query like below:


DCI_Crosstab:

TRANSFORM Count(Doc_List.[Document Number]) AS [CountOfDocument Number]
SELECT Doc_List.[Owner Discipline Code], Doc_List.Status, Count(Doc_List.[Document Number]) AS [Total Of Issued Document Number]
FROM Doc_List
WHERE (((Doc_List.[Transmittal No]) Is Not Null Or (Doc_List.[Transmittal No])<>""))
GROUP BY Doc_List.[Owner Discipline Code], Doc_List.Status
PIVOT Doc_List.[Comment Status];

Total_MDR:

SELECT Doc_List.[Owner Discipline Code], (SELECT Count([Document Number]) FROM Doc_List AS T1 WHERE  [T1].[Owner Discipline Code]=[Doc_List].[Owner Discipline Code]) AS [Total Doc], (SELECT Count([Document Number]) FROM Doc_List AS T1 WHERE (T1.[Construction Required_Y_N]="Y") And ([T1].[Owner Discipline Code]=[Doc_List].[Owner Discipline Code])) AS [Total Doc_Need_AFC], (SELECT Count([Document Number]) FROM Doc_List AS T1 WHERE (T1.[Construction Required_Y_N]="N" OR T1.[Construction Required_Y_N] IS null) And ([T1].[Owner Discipline Code]=[Doc_List].[Owner Discipline Code])) AS [Total Doc_Need_FIN]
FROM Doc_List
GROUP BY Doc_List.[Owner Discipline Code];

When I try to join these two queris as below:

SELECT DCI_Crosstab.[Owner Discipline Code], Total_MDR.[Total Doc], DCI_Crosstab.[Total Of Issued Document Number]
FROM DCI_Crosstab INNER JOIN Total_MDR ON DCI_Crosstab.[Owner Discipline Code] = Total_MDR.[Owner Discipline Code];

I got error : microsoft access can not recognize (Doc_List.[Owner Discipline Code]) as a valid field name or expression

 

[masoud_sedighy]

Thanks, I fixed first error.
about counting with subselect, I do not know other way, because it needed multiple filter, I did like this.

I deleted spaces of fields but still has same error.