Replace Parentheses with Bracket in Subquery

[WharfRat5ddf18]

This is a simple example to illustrate the problem ... I have 3 tables with
employee data, hardware sales data, and software sales data; the query is
summing the amount of slaes for each employee. The table and field names are
very simple (no spaces, no unusual characters).

I have the following query that I enter into the SQL View window of
Microsoft Access 2003 SP3.

SELECT SalesPerson.EmpNo, SalesPerson.LastName,
H.HWCnt, H.HWAmt, S.SWCnt, S.SWAmt
FROM (SalesPerson
INNER JOIN
(SELECT EmpNo, COUNT(EmpNo) as HWCnt, SUM(Amount) as HWAmt
FROM HardwareSales GROUP BY EmpNo) AS H
ON SalesPerson.EmpNo = H.EmpNo)

INNER JOIN
(SELECT EmpNo, COUNT(EmpNo) as SWCnt, SUM(Amount) as SWAmt
FROM SoftwareSales GROUP BY EmpNo) AS S
ON SalesPerson.EmpNo = S.EmpNo;

The query runs fine. However, when I close the query and re-open, I get
this, which will not run:

SELECT SalesPerson.EmpNo, SalesPerson.LastName,
H.HWCnt, H.HWAmt, S.SWCnt, S.SWAmt
FROM (SalesPerson
INNER JOIN
[SELECT EmpNo, COUNT(EmpNo) AS HWCnt, SUM(Amount) AS HWAmt
FROM HardwareSales GROUP BY EmpNo; ] AS H
ON SalesPerson.EmpNo=H.EmpNo)

INNER JOIN
[SELECT EmpNo, COUNT(EmpNo) AS SWCnt, SUM(Amount) AS SWAmt
FROM SoftwareSales GROUP BY EmpNo; ] AS S
ON SalesPerson.EmpNo=S.EmpNo;

Note how Access has replaced the parentheses with square brackets around the
subqueries. This happens every time when I have subqueries.

How do I prevent Access from inserting brackets around subqueries?

Thanks!

Larry

 

[Clifford Bass]

Hi Larry,

Prevent it by not using subqueries in that way. I think you can
probably accomplish what you need with a straight summary query. This one
should work and will show everyone regardless of the presence of absence of
sales of a particular type. If you want only those with both types of sales
change the joins to inner joins.

SELECT SP.EmpNo, SP.LastName, Count(HW.EmpNo) AS HWCnt, Sum(HW.Amount) AS
HWAmt, Count(SW.EmpNo) AS SWCnt, Sum(SW.Amount) AS SWAmt
FROM (SalesPerson AS SP LEFT JOIN HardwareSales AS HW ON SP.EmpNo =
HW.EmpNo) LEFT JOIN SoftwareSales AS SW ON SP.EmpNo = SW.EmpNo
GROUP BY SP.EmpNo, SP.LastName;

Alternatively, if you need the subqueries, create them as separate
queries and use those queries as the input "tables" to the main query.

Hope that helps,

Clifford Bass