Invalid bracket when editing SQL query

[bg17067 via AccessMonster.com]

I have this SQL query in Access 2003 and it works perfectly. However, I am
trying to add an additional WHERE statement to the query.

Here is the exisitng query:

SELECT Manufacturer, AccountNumber, Customer, SUM(CurrAmt) AS [Current], SUM
(PrevAmt) AS [Prior]
FROM [SELECT Manufacturer, AccountNumber, Customer, SUM([Shipped Amount]) AS
CurrAmt, 0 AS PrevAmt

FROM Tbl_Commission

WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current End
Date]

GROUP BY Manufacturer, AccountNumber, Customer

UNION ALL

SELECT Manufacturer, AccountNumber, Customer, 0 AS CurrAmt, SUM([Shipped
Amount]) AS PrevAmt

FROM Tbl_Commission

WHERE ShippedDate BETWEEN [Enter Prior Start Date] AND [Enter Prior End Date]

GROUP BY Manufacturer, AccountNumber, Customer]. AS X
GROUP BY Manufacturer, AccountNumber, Customer;

I am trying to add an additional WHERE statement of AccountNumber = [Enter
Account Number]. When I try to add that in directly under the other WHERE
statement I get an "invalid bracketing" error.

Any help would be greatly appreciated!!! Thanks in advance!!
Brian

 

[John W. Vinson]

I have this SQL query in Access 2003 and it works perfectly. However, I am
trying to add an additional WHERE statement to the query.

Here is the exisitng query:

SELECT Manufacturer, AccountNumber, Customer, SUM(CurrAmt) AS [Current], SUM
(PrevAmt) AS [Prior]
FROM [SELECT Manufacturer, AccountNumber, Customer, SUM([Shipped Amount]) AS
CurrAmt, 0 AS PrevAmt

FROM Tbl_Commission

WHERE ShippedDate BETWEEN [Enter Current Start Date] AND [Enter Current End
Date]

GROUP BY Manufacturer, AccountNumber, Customer

UNION ALL

SELECT Manufacturer, AccountNumber, Customer, 0 AS CurrAmt, SUM([Shipped
Amount]) AS PrevAmt

FROM Tbl_Commission

WHERE ShippedDate BETWEEN [Enter Prior Start Date] AND [Enter Prior End Date]

GROUP BY Manufacturer, AccountNumber, Customer]. AS X
GROUP BY Manufacturer, AccountNumber, Customer;

I am trying to add an additional WHERE statement of AccountNumber = [Enter
Account Number]. When I try to add that in directly under the other WHERE
statement I get an "invalid bracketing" error.

Any help would be greatly appreciated!!! Thanks in advance!!
Brian

The problem is that you're trying to use a subquery - in brackets - which
itself contains bracketed fieldnames. No can do! Your subquery may need to be
pulled out and saved as another query, and you can then use that query name as
the subquery.

Also, a query can't have two WHERE clauses. If you have two or more criteria,
you would use one WHERE clause with multiple logical expressions joined by AND
or OR.

 

[David W. Fenton]

The problem is that you're trying to use a subquery - in brackets
- which itself contains bracketed fieldnames. No can do! Your
subquery may need to be pulled out and saved as another query, and
you can then use that query name as the subquery.

BTW, Jet is smart enough to prompt you only once for a parameter
that has an identical definition in two levels of a query. That is,
if you had a parameter in your top-level query and declared the same
parameter exactly the same in a saved query used as a subquery,
you'd be prompted for the parameter value only once.

However, if I'm not mistaken, you'd get prompted twice if you used
the parameter in two saved queries below the top-level query. But I
could be misremembering that.