• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5256
  • Last Modified:

Access Union Query in Excel

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
JJINFM
Asked:
JJINFM
  • 2
1 Solution
 
RyanProject Engineer, ElectricalCommented:
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
 
JJINFMAuthor Commented:
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
 
Jerry PaladinoCommented:
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
 
JJINFMAuthor Commented:
Brilliant!!  That worked!

Thanks for your help
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now