Improve company productivity with a Business Account.Sign Up

x
?
Solved

Access Union Query in Excel

Posted on 2013-11-15
4
Medium Priority
?
5,459 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

606 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