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);

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

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

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.

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?

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.