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

 

[Pat Hartman]

WHERE (((Doc_List.[Transmittal No]) Is Not Null Or (Doc_List.[Transmittal No])<>""))

This needs to be AND. You want BOTH conditions to be true.

You don't need to use a subselect to do the count.

Best practice is to make object names that conform to programming standards. i.e. Letters, numbers, and the underscore ONLY. No special characters or embedded spaces.

Fix those two errors and we'll see where we are.

 

[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.

 

[Pat Hartman]

Post two spreadsheets. One from each of the two queries and we'll be able to see why the join isn't working if you can't see the problem after looking at the actual resultsets.