Solved

Access Union Query in Excel

Posted on 2013-11-15
4
3,816 Views
Last Modified: 2013-11-18
Hi Experts,

I have a union query in Access 2010 that I would like to use in Excel 2010.  I can't seem to get this query to show up in the Data Connection.  If I try MS Query I get "Too few parameters. Expected 3."

Here is the sql for the union query:

SELECT [Store Info].greatplains AS Store, dbo_dpvHstSalesTotals.DateOfBusiness, "Cash" AS [Desc], dbo_dpvHstSalesTotals.Deposit AS Amt, "" AS TypeID
FROM [Store Info] INNER JOIN dbo_dpvHstSalesTotals ON [Store Info].RadiantNbr = dbo_dpvHstSalesTotals.FKStoreId
WHERE ((([Store Info].greatplains)="FTMY") AND ((dbo_dpvHstSalesTotals.DateOfBusiness)>#09/01/2013#))
ORDER BY dbo_dpvHstSalesTotals.DateOfBusiness DESC;

UNION ALL SELECT CompMaster.STORE, dbo_dpvHstGndTender.DateOfBusiness, UCase([compmaster].[NAME]) AS [Desc], ([Amount]+[TIP]) AS AMT, dbo_dpvHstGndTender.TypeId
FROM CompMaster INNER JOIN ([Store Info] INNER JOIN dbo_dpvHstGndTender ON [Store Info].RadiantNbr = dbo_dpvHstGndTender.FKStoreId) ON (CompMaster.ID = dbo_dpvHstGndTender.TypeId) AND (CompMaster.STORE = [Store Info].greatplains)
WHERE (((CompMaster.STORE)="FTMY") AND ((dbo_dpvHstGndTender.DateOfBusiness)>#09/01/2013#) AND ((dbo_dpvHstGndTender.Type)=3));

Any help is appreciated

jj
0
Comment
Question by:JJINFM
  • 2
4 Comments
 
LVL 13

Expert Comment

by:MrBullwinkle
ID: 39652135
Is dbo_dpvHstGndTender.TypeId a string?  In your first select you're setting it as an empty string.  Typically IDs are integer types (use 0, -1, or null as undefined values).

May not be the issue, but may be an issue.
0
 

Author Comment

by:JJINFM
ID: 39652346
If I remove that column from the 1st query I get a message that states "The number of columns in the two selected tables or queries of a union query do not match"

I just added that column to the first query to get to the same number of columns that the second query has.  There is a value returned in the second query.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 39653123
JJ,
First off, verify the query works as you expect in Access.

I assume you are using, Excel's Data Ribbon, "From Access" in the "Get External Data" section...  When this returns the list of table and views, it does not include UNION queries in the list.  Not sure why, but from my experience it does not.   Pick any of your existing tables/views in the list.   This will bring up the Import Data dialog box.  Press the Properties button and then select the Definition tab on the Connection Properties dialog that is displayed.   In the Command Text box of that dialog you should see the name of the Table or View you selected.   Replace that name with the name of your UNION query and press OK.   The result set of the UNION query should be returned to your Excel sheet.

HTH,
Jerry
0
 

Author Comment

by:JJINFM
ID: 39656444
Brilliant!!  That worked!

Thanks for your help
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question