Converting SQL Query to Access Query - SELECT within SELECT

Question

I have a query in SQL Server that I am trying to convert to a query in MS-Access 2003. The query is designed to be used as the basis for a report. The report has two fields .. 'Cases Assigned' and 'Cases Closed'.

SELECT 
(SELECT COUNT(*) 
FROM CaseDetail 
WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009') as 'Cases Assigned',
(SELECT COUNT(*) 
FROM CaseDetail 
WHERE CaseClosedDate BETWEEN '1/1/2008' AND '1/1/2009') as 'Cases Closed'

I am having difficulty using the SQL in Access 2003. I have replaced the ' characters with # for Access's sake but still no joy. Does Access have a problem with SELECT within SELECT statements? The error I get from Access is less than helpful.

Reserved error (-3205); there is no message for this error

Also, what if the SQL statement was such that data needed to be obtained from more than one table. For example ...

SELECT 
(SELECT COUNT(*) 
FROM AssignedCases
WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009') as 'Cases Assigned',
(SELECT COUNT(*) 
FROM ClosedCases
WHERE CaseClosedDate BETWEEN '1/1/2008' AND '1/1/2009') as 'Cases Closed'

This works in SQL without issue, but not in Access.

Answer 1

3205 error is Too many crosstab column headers .

Does this query solution for you:

SELECT 'Cases Asssigned' as Type, COUNT(*) 
FROM CaseDetail 
WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009'
UNION
SELECT 'Cases Closed'as Type, COUNT(*) 
FROM CaseDetail 
WHERE CaseClosedDate BETWEEN '1/1/2008' AND '1/1/2009')

result will in two rows instead of one.

Answer 2

I haven't a Windows machine to test on at the moment, but something like this should work.

SELECT SUM(IIF(CaseAssignedDate 
           BETWEEN #1/1/2008# AND #1/1/2009#, 1, 0)) AS CasesAssigned,
       SUM(IIF(CaseClosedDate 
           BETWEEN #1/1/2008# AND #1/1/2009#, 1, 0)) AS CasesClosed
  FROM CaseDetail 

In my experience it is generally preferable, where possible, to avoid using subqueries in Access.

---

Edit:
Responding to your comment, I just tested that Access allows this alternative method:

SELECT *
  FROM
(
SELECT 'CasesAssigned', COUNT(*) AS Total
  FROM AssignedCases
 WHERE CaseAssignedDate BETWEEN #1/1/2008# AND #1/1/2009#

UNION ALL

SELECT 'ClosedCases', COUNT(*) AS Total
  FROM ClosedCases
 WHERE CaseClosedDate BETWEEN #1/1/2008# AND #1/1/2009#
);

---

Edit2:
littlegreen's answer will work if you need a one-row resultset.

Answer 3

Nested queries are allowed in Access as well as in SQL server, but at least in SQL server it requires that you set a dummy alias for your nested query, and all columns in your nested query need a name. This might have caused the error.

I would suggest following query:

SELECT q1.CasesAssigned, q2.CasesClosed
FROM 
(SELECT COUNT(*) AS CasesAssigned
FROM CaseDetail 
WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009') as q1,
(SELECT COUNT(*) AS CasesClosed
FROM CaseDetail 
WHERE CaseClosedDate BETWEEN '1/1/2008' AND '1/1/2009') as q2

Answer 4

I don't know where you're getting this error message, but your problem is trying to execute a Select statement without at least a table or query. You'll need some sort of 'dummy' table with just one record to accomplish this.

SELECT  
    (SELECT COUNT(*)  
     FROM CaseDetail  
     WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009'
    ) as 'Cases Assigned', 
    (SELECT COUNT(*)  
     FROM CaseDetail  
     WHERE CaseClosedDate BETWEEN '1/1/2008' AND '1/1/2009'
    ) as 'Cases Closed' 
FROM DummyTableWithOneRecord;

Answer 5

Since you have a query which works in SQL Server, create an Access pass-through query which uses that query (in SQL Server) and returns the result set to Access.

Alternatively, create a SQL Server view based on that SELECT statement and link to the view from Access.

Since it already works in SQL Server, I don't see any added value from re-creating the query in Access.