Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 24th, 2007, 09:02 AM
Authorized User
 
Join Date: May 2007
Location: Fairfax, va, USA.
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Union query in Access??

Hi all. I am trying to do a UNION query in Access to call in a report. I am used to SQL Server and writing my own queries without having the designer. Anyway... I have started using the SQL code in Access but my UNION query is still not working. I need to read against the same table but get different results. I need the SQL below or something like it. The table has FundingCategory of value 1 or 4 that must be added together(second query in UNION) and FundingCategory of 2 and 3 that stand alone(do not need to be added together) in the first query of UNION. It is giving me an error when trying to run it. I may be doing something wrong the query but can't see it. I am not sure if there is some limitation in Access 2003.

Thanks
Lisabb

SELECT IDCode, AmountGranted AS AmountGranted, FundingCategory, fiscalyear
FROM tblGrants INNER JOIN tblPrograms ON tblGrants.ProgramID = tblPrograms.ProgramID
WHERE (((tblGrants.IDCode)=[IDCode]) AND (([tblGrants]![ProgramID])=[tblPrograms]![ProgramID])) And (tblGrants!FundingCategory <> 1 and tblGrants!FundingCategory <> 4)
UNION SELECT IDCode, Sum(AmountGranted] AS AmountGranted, FundingCategory, fiscalyear
FROM tblGrants INNER JOIN tblPrograms ON tblGrants.ProgramID = tblPrograms.ProgramID
WHERE (((tblGrants.IDCode)=[IDCode]) AND (([tblGrants]![ProgramID])=[tblPrograms]![ProgramID])) And (tblGrants!FundingCategory = 1 or tblGrants!FundingCategory = 4);
Reply With Quote
  #2 (permalink)  
Old October 24th, 2007, 01:30 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 62%
Activity: 62% Activity: 62% Activity: 62%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think you may have better luck if you create the sub queries as stand alone queries, and then use the UNION statement against them, rather than doing it all in one query. Access is ANSI 89, and SQL Server is ANSI 92, so some of what you are used to will not be available in Access. Anyway, that is how I usually do such things. Other posters may have more elegant solutions.



mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old October 24th, 2007, 01:36 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 62%
Activity: 62% Activity: 62% Activity: 62%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I took my own advice and looked this up on Wrox Books 24x7. I found a good entry in Expert One-on-One: Microsoft Access Application Development by Helen Feddema:

"To start making a union query, make a select query based on one of the tables, and create any concatenated fields you need in Design view (you won’t have this opportunity once you convert the query to a union query). In this case, I made a select query based on tblCustomers, with CustomerID, address fields, and a calculated ShipName field...

Next, switch to SQL view. The initial SQL statement of the query is:

SELECT tblCustomers.CustomerID, [ContactFirstName] & “ “ & [ContactLastName]
AS ShipName, tblCustomers.BillingAddress, tblCustomers.City, tblCustomers.StateOrProvince,
tblCustomers.PostalCode FROM tblCustomers;

(She then removes the table names in front of each field name)

The syntax for a concatenated or aliased field is in the opposite order from that used in Design view: the expression comes first, then the AS keyword, then the field name ([ContactFirstName] & “ “ & [ContactLastName] AS ShipName).

Next, create another select query based on the second table (in this case, tblShippingAddresses), including all the fields except ShipDepartment, and switch to SQL view. After trimming out the table names, the second SQL statement is as follows:

SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress,
ShipCity, ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;
To convert the query into a union query, in the first query’s SQL statement, remove the final semicolon, and type in UNION followed by a space, on a new line under the SQL statement. Switch to the second query, copy its SQL statement, return to the first query, and paste the text in after UNION. The resulting SQL statement is listed below:

SELECT CustomerID, [ContactFirstName] & “ “ & [ContactLastName] AS ShipName,
BillingAddress, City, StateOrProvince, PostalCode FROM tblCustomers
UNION SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress, ShipCity,
ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;
There is still some work to do. The component queries in a union query must have the same number of columns (fields), and the matching fields must be of matching data types (however, they don’t have to have the same names). There is no ShipAddressID field in tblCustomers, so I created a dummy field with a value of 0 for this purpose. Similarly, I made a dummy field for AddressIdentifier, with a value of “Billing Address.” I also aliased BillingAddress as Address, to make a more generic field name in the union query (the field names of the union query are the ones in the first component query). The final SQL statement is as follows:

SELECT 0 AS ShipAddressID, CustomerID, “Billing Address” AS AddressIdentifier,
[ContactFirstName] & “ “ & [ContactLastName] AS ShipName, BillingAddress AS Address, City,
StateOrProvince, PostalCode FROM tblCustomers
UNION SELECT ShipAddressID, CustomerID, AddressIdentifier, ShipName, ShipAddress, ShipCity,
ShipStateOrProvince, ShipPostalCode FROM tblShippingAddresses;
Now the query can be saved with the tag quni"

(She uses quni to designate a Union query, but you can use your own naming convention.)

HTH




mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #4 (permalink)  
Old October 24th, 2007, 04:00 PM
Authorized User
 
Join Date: May 2007
Location: Fairfax, va, USA.
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks mmcdonal for the reply. I have now abandoned that (for now) and am just pulling all rows back. I have a report in Access 2003. In that report, I have a subreport that pulls totals by funding type and by year. The table I pull from is set up with cols Fundingcategory, grantAmount, fiscalYear, PHACode. I have a parameter that I send in (from the main report) for PHACode. Or at least I am "trying" to send a parameter in. My query now is one that looks like this:

PARAMETERS PHACode Text ( 255 );
TRANSFORM sum(tblGrants.AmountGranted) AS AmountGranted
SELECT tblGrants.fundingcategory, tblPrograms.fiscalyear
FROM tblGrants INNER JOIN tblPrograms ON tblGrants.ProgramID = tblPrograms.ProgramID
WHERE tblGrants.HACode = [PHACode]
GROUP BY tblGrants.fundingcategory
PIVOT tblPrograms.fiscalyear;

I am using the PIVOT and Transform in this query. Right now, I am trying to set the subreport fields to the corresponding values from the query output. The problem is when I set the reportsource to my query... a message box pops up asking for the parameter (i think because of the Parameter statement that is first in the query). If I get rid of that statement, the query doesn't work. When the message comes up, it causes my application to end. Any suggestions on how to do this? I somehow need to delay the need for the parameter until run time so the report can send it in.
Reply With Quote
  #5 (permalink)  
Old October 24th, 2007, 07:38 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Lisa,

Are you using a dialog form to capture the user's parameter? You could try capturing their selection (to capture PHACode- probably from a combo box), drop that into a variable (ie: strInput) then concatenate that into your WHERE clause ("WHERE tblGrants.HACode ='"& strInput & "'"). Does that work?

Reply With Quote
  #6 (permalink)  
Old October 24th, 2007, 10:04 PM
Authorized User
 
Join Date: May 2007
Location: Fairfax, va, USA.
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Loralee,

I am using a variable from a form. The main report already uses this variable and I just want the subreport to use it as well. I am creating the subreport as a stand alone report and then when I go to main report I am going to insert it. I am going to just bring in the stand alone report as is to be the subreport. A reference book says you can do this but my query keeps asking for a parameter even in design mode.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Parameter in a Union Query bright_mulenga Access VBA 0 January 17th, 2008 02:52 AM
UNION QUERY Help Corey Access 1 October 27th, 2006 05:29 PM
UNION query. rupen Access 3 April 28th, 2006 02:49 AM
Insert into error with union query in Access 2002 roniestein Access 8 December 21st, 2004 06:47 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM



All times are GMT -4. The time now is 08:46 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.