Solved

Access Union Query in Excel

Posted on 2013-11-15
4
4,022 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL String-handling question ... 7 53
Cannot locate cell 15 42
copy down array 24 35
Excel file size grew while there isn't any data in it. 3 24
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
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…

739 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