Strangeness... A query is refusing to return the desired results. It's obviously a syntax problem but I'm not sure where and I'm hoping someone can suggest an approach that will work.
Working with Access 2003 that is pulling data from linked MS SQL tables on a SQL 2005 server.
In SQL Server Management Studio, this query:
Produces a table with three columns similar to this:
SecNum Section Name Total Assignments Received
2 Michelle 367
6 Russ 201
1 Roy 267
16 Elaine 216
21 Renee 141
3 Dan 17
...Which is the desired results.
In Access, this query will produce the first and third column:
However, when adding in the code to get the middle, Section Name, column:
An error message is produced indicating the query is too complex.
Surely there must be a way around it so the Access query will produce a datasheet with the same three columns as above. Any ideas would be appreciated.
Thanks,
John
Working with Access 2003 that is pulling data from linked MS SQL tables on a SQL 2005 server.
In SQL Server Management Studio, this query:
Code:
SELECT SecNum, (SELECT SecName FROM dbo.tbl_Sec WHERE SecID = dbo.tbl_MCHS.SecNum) AS [Section Name],
Count(*) AS [Total Assignments Received]
FROM dbo.tbl_MCHS
WHERE DateAssign between '2008-04-01 00:00:00:000' and '2008-04-30 00:00:00:000'
GROUP BY SecNum;
Produces a table with three columns similar to this:
SecNum Section Name Total Assignments Received
2 Michelle 367
6 Russ 201
1 Roy 267
16 Elaine 216
21 Renee 141
3 Dan 17
...Which is the desired results.
In Access, this query will produce the first and third column:
Code:
SELECT tbl_MCHS.SecNum, Count(*) AS [Total Assignments Received]
FROM tbl_MCHS
WHERE (((tbl_MCHS.DateAssign) Between [Forms]![frm_Reports]![Begin] And [Forms]![frm_Reports]![End]))
GROUP BY tbl_MCHS.SecNum;
However, when adding in the code to get the middle, Section Name, column:
Code:
(SELECT SecName FROM tbl_Sec WHERE SecID = tbl_MCHS.SecNum) AS [Section Name],
Surely there must be a way around it so the Access query will produce a datasheet with the same three columns as above. Any ideas would be appreciated.
Thanks,
John