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

 

[WharfRat5ddf18]

Hi Cliff,

Thansk for the reply. Unfortunately, your query does not work as an employee
may have multiple entries in the HardwareSales and SoftwareSales tables.

Regardless, I understand that using seperate Access queries in place of
subqueries will work. I was hoping to find a soution that allows subqueries.

Is there a patch that repairs the Access parser?

Thanks again!

Larry

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

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,

Did you try it? Because I tried it with multiple same-employee entries
in both of the child tables. And it worked.

As to a patch, that I could not tell. It may be useful to submit a bug
report to Microsoft that includes enough detail for them to replicate it.

Clifford Bass

 

[Clifford Bass]

Hi Larry,

My apologies :-( I am wrong there. However should it help, in my
Access 2003 with SP3 it changes your statement to this:

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;

Which is a little different from what it does to yours. And in Access
2007 it keeps it as entered:

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;

Clifford Bass