Syntax Problem

  • Thread starter Thread starter DBarker
  • Start date Start date
D

DBarker

below is a union query that am using, but what I want is
the user to specify what dept. # and Budget year they
want to display. I have tried to combine but keep
getting syntax error messages. Does anyone know the
correct syntax so that the user only has to enter the
information one. I initially had the criteria listed
separately in each select statement which did work but it
required them to enter the same information twice.

Any help would be appreciated.

Debbie

----------------------------------------------------------


SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], Sum((Nz([SumOfAmount]))) AS Amount,
Sum((Nz([Annual Budget]))) AS Budget
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]
GROUP BY [Annual Budget Query].[Account #], [Annual
Budget Query].Name_of_Account, [Annual Budget Query].
[Department #], [Annual Budget Query].Department_Name,
[Annual Budget Query].[Budget Year]

UNION

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year], Sum((Nz
([SumOfAmount]))) AS Amount, Sum((Nz([Annual Budget])))
AS Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year]


HAVING ((([Annual Budget Query].[Department #]OR[Account
to Budget].[Department #])=[Enter Department #]) OR
(([Annual Budget Query].[Budget Year], (Account Charges
Query].[Budget Year])=[Enter Budget Year]));
 
Well, you should include a PARAMETERS clause and use WHERE in both sides of
the UNION. Assuming the parameters are both integers, try this:

PARAMTERS [Enter Department #] Short, [Enter Budget Year] Short;
SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], Sum((Nz([SumOfAmount]))) AS Amount,
Sum((Nz([Annual Budget]))) AS Budget
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]
WHERE (([Annual Budget Query].[Department #] = [Enter Department #])
OR ([Account Charges Query].[Department #] = [Enter Department #]))
AND (([Annual Budget Query].[Budget Year] = [Enter Budget Year]) AND
([Account Charges Query].[Budget Year] = [Enter Budget Year]))
GROUP BY [Annual Budget Query].[Account #], [Annual
Budget Query].Name_of_Account, [Annual Budget Query].
[Department #], [Annual Budget Query].Department_Name,
[Annual Budget Query].[Budget Year]

UNION

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year], Sum((Nz
([SumOfAmount]))) AS Amount, Sum((Nz([Annual Budget])))
AS Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
WHERE (([Annual Budget Query].[Department #] = [Enter Department #])
OR ([Account Charges Query].[Department #] = [Enter Department #]))
AND (([Annual Budget Query].[Budget Year] = [Enter Budget Year]) AND
([Account Charges Query].[Budget Year] = [Enter Budget Year]))
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year];

Note that I'm assuming your convoluted HAVING clause wants:

The selected department number from either of the two queries in the FROM
clause.

The Budget Year in both queries to match the selected budget year.

When you're having problems like this, try starting over by creating each
query independently in two query grids. Make sure each query runs by
itself. Then, switch to SQL view in both, add a UNION keyword in one and
the SQL from the other. (And remove the extra semi-colon.)


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
DBarker said:
below is a union query that am using, but what I want is
the user to specify what dept. # and Budget year they
want to display. I have tried to combine but keep
getting syntax error messages. Does anyone know the
correct syntax so that the user only has to enter the
information one. I initially had the criteria listed
separately in each select statement which did work but it
required them to enter the same information twice.

Any help would be appreciated.

Debbie

----------------------------------------------------------


SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], Sum((Nz([SumOfAmount]))) AS Amount,
Sum((Nz([Annual Budget]))) AS Budget
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]
GROUP BY [Annual Budget Query].[Account #], [Annual
Budget Query].Name_of_Account, [Annual Budget Query].
[Department #], [Annual Budget Query].Department_Name,
[Annual Budget Query].[Budget Year]

UNION

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year], Sum((Nz
([SumOfAmount]))) AS Amount, Sum((Nz([Annual Budget])))
AS Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year]


HAVING ((([Annual Budget Query].[Department #]OR[Account
to Budget].[Department #])=[Enter Department #]) OR
(([Annual Budget Query].[Budget Year], (Account Charges
Query].[Budget Year])=[Enter Budget Year]));
Click to expand...
 
Ummm. Just figured out what it is you're trying to do - get all Budget
records with any matching Account Charges records UNION all Account Charges
with any matching Budget. Basically, a FULL OUTER JOIN. So, I need to fix
the suggested WHERE clause and add an Is Null test to the second one to make
it run faster:

PARAMTERS [Enter Department #] Short, [Enter Budget Year] Short;
SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], Sum((Nz([SumOfAmount]))) AS Amount,
Sum((Nz([Annual Budget]))) AS Budget
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]
WHERE ([Annual Budget Query].[Department #] = [Enter Department #])
AND ([Annual Budget Query].[Budget Year] = [Enter Budget Year])
GROUP BY [Annual Budget Query].[Account #], [Annual
Budget Query].Name_of_Account, [Annual Budget Query].
[Department #], [Annual Budget Query].Department_Name,
[Annual Budget Query].[Budget Year]

UNION ALL

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year], Sum((Nz
([SumOfAmount]))) AS Amount, Sum((Nz([Annual Budget])))
AS Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
WHERE ([Account Charges Query].[Department #] = [Enter Department #])
AND ([Account Charges Query].[Budget Year] = [Enter Budget Year])
AND ([Annual Budget Query].[Account #] IS NULL)
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year];


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
DBarker said:
below is a union query that am using, but what I want is
the user to specify what dept. # and Budget year they
want to display. I have tried to combine but keep
getting syntax error messages. Does anyone know the
correct syntax so that the user only has to enter the
information one. I initially had the criteria listed
separately in each select statement which did work but it
required them to enter the same information twice.

Any help would be appreciated.

Debbie

----------------------------------------------------------


SELECT [Annual Budget Query].[Account #], [Annual Budget
Query].Name_of_Account, [Annual Budget Query].[Department
#], [Annual Budget Query].Department_Name, [Annual Budget
Query].[Budget Year], Sum((Nz([SumOfAmount]))) AS Amount,
Sum((Nz([Annual Budget]))) AS Budget
FROM [Annual Budget Query] LEFT JOIN [Account Charges
Query] ON [Annual Budget Query].[Account #] = [Account
Charges Query].[Account #]
GROUP BY [Annual Budget Query].[Account #], [Annual
Budget Query].Name_of_Account, [Annual Budget Query].
[Department #], [Annual Budget Query].Department_Name,
[Annual Budget Query].[Budget Year]

UNION

SELECT [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year], Sum((Nz
([SumOfAmount]))) AS Amount, Sum((Nz([Annual Budget])))
AS Budget
FROM [Account Charges Query] LEFT JOIN [Annual Budget
Query] ON [Account Charges Query].[Account #] = [Annual
Budget Query].[Account #]
GROUP BY [Account Charges Query].[Account #], [Account
Charges Query].Name_of_Account, [Account Charges Query].
[Department #], [Account Charges Query].Department_Name,
[Account Charges Query].[Budget Year]


HAVING ((([Annual Budget Query].[Department #]OR[Account
to Budget].[Department #])=[Enter Department #]) OR
(([Annual Budget Query].[Budget Year], (Account Charges
Query].[Budget Year])=[Enter Budget Year]));
Click to expand...
 
Back
Top