Union Query issue

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

DBarker

Below is my union query. Now I am trying to set it up
that the user can ask for a specific department and
budget year. If I take the criteria out of one of the
select queries it brings back multiple entries of the
same data. But if I leave the criteria in I get asked
twice to enter a department number. Any ideas how I can
fix this problem?

----------------------------------------------------
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]
HAVING ((([Annual Budget Query].[Department #])=[Enter
Department #]) AND (([Annual Budget Query].[Budget Year])=
[Enter 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 ((([Account Charges Query].[Department #])=[Enter
Department Number]) AND (([Account Charges Query].[Budget
Year])=[Enter Budget Year]));
 
In one SELECT statment you used:

HAVING ((([Annual Budget Query].[Department #])=[Enter Department #])

... and in the other you used:

HAVING ((([Annual Budget Query].[Department #])=[Enter Department Number])

If you use the same parameter name in both places, you should get prompted
only once. So, either change the first one to [Enter Department Number] or
the second to [Enter Department #].

--
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)
 
Hi,

Have you tried setting "[Enter Department Number]" as a Parameter?

PARAMETERS [Enter Department Number] Text ( 255 );

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]
HAVING ((([Annual Budget Query].[Department #])=[Enter
Department #]) AND (([Annual Budget Query].[Budget Year])=
[Enter 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 ((([Account Charges Query].[Department #])=[Enter
Department Number]) AND (([Account Charges Query].[Budget
Year])=[Enter Budget Year]));



I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| Content-Class: urn:content-classes:message
| From: "DBarker" <[email protected]>
| Sender: "DBarker" <[email protected]>
| Subject: Union Query issue
| Date: Tue, 24 Feb 2004 12:27:50 -0800
| Lines: 42
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcP7FKxXO2N7NYnDSYWi0wg0ZqiFoQ==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:191285
| NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
| X-Tomcat-NG: microsoft.public.access.queries
|
| Below is my union query. Now I am trying to set it up
| that the user can ask for a specific department and
| budget year. If I take the criteria out of one of the
| select queries it brings back multiple entries of the
| same data. But if I leave the criteria in I get asked
| twice to enter a department number. Any ideas how I can
| fix this problem?
|
| ----------------------------------------------------
| 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]
| HAVING ((([Annual Budget Query].[Department #])=[Enter
| Department #]) AND (([Annual Budget Query].[Budget Year])=
| [Enter 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 ((([Account Charges Query].[Department #])=[Enter
| Department Number]) AND (([Account Charges Query].[Budget
| Year])=[Enter Budget Year]));
|
|
 
Below is my union query. Now I am trying to set it up
that the user can ask for a specific department and
budget year. If I take the criteria out of one of the
select queries it brings back multiple entries of the
same data. But if I leave the criteria in I get asked
twice to enter a department number. Any ideas how I can
fix this problem?
Click to expand...

It may be convenient to use a Form reference as a criterion: create a
small unbound form frmCrit with a combo box cboDept and use a
criterion of

=[Forms]![frmCrit]![cboDept])
 
Back
Top