Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Union Query in Excel

Posted on 2013-11-15
4
Medium Priority
?
4,856 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:Ryan
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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

916 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