Access -> Excel - Microsoft Query Problem

[pgraju]

Hi all, I seem to be getting this error when I try to import a query from MS Access to Excel:

Too Few Parameters. Expected 1.

Now, it runs fine in Access and seems to be a Microsoft Query problem, I know there are some issues around the performance and capability of Microsoft Query but hopefully someone can have a look at my query and see if they can identify my "too few parameters"

(Sorry about the largeness of the query...)

SELECT YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]
FROM YPOL_LoadTbl
GROUP BY YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Canvass Code], YPOL_LoadTbl.[Issue ID]
UNION
SELECT "ALL" AS [Channel Done], "ALL" AS [Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]
FROM YPOL_LoadTbl
GROUP BY YPOL_LoadTbl.[Issue ID]
UNION
SELECT YPOL_LoadTbl.[Channel Done], "ALL" AS [Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]
FROM YPOL_LoadTbl
GROUP BY YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Issue ID]
UNION
SELECT YPOL_LoadTbl.[Channel Done], "ALL" AS [Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]
FROM YPOL_LoadTbl
GROUP BY YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Issue ID]
UNION 
SELECT "ALL" AS [Channel Done], YPOL_LoadTbl.[Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]
FROM YPOL_LoadTbl
GROUP BY YPOL_LoadTbl.[Canvass Code], YPOL_LoadTbl.[Issue ID];

Thanks!

 

[Guus2005]

store your query (query1) and run it. It will prompt you to enter the parameter e.g. YPOL_LoadTbl.[Canvass Code].
This means that there isn't a field in your tables/subqueries with this name. You probably have a typo.

UNION always filters out double records. When you're sure you don't have any, use UNION ALL. It is faster with less overhead.

HTH

 

[pgraju]

store your query (query1) and run it. It will prompt you to enter the parameter e.g. YPOL_LoadTbl.[Canvass Code].
This means that there isn't a field in your tables/subqueries with this name. You probably have a typo.

UNION always filters out double records. When you're sure you don't have any, use UNION ALL. It is faster with less overhead.

HTH

Guus,

After storing the SQL query into a new query it still ran fine. The only problem is when I try to import it into Excel using Microsoft Query...

Could there be another reason for this?

 

[RoyVidar]

Sometimes MS Query wont recognize different querytypes, such as Unions. One trick that sometimes work, is to create another query based on that - just a select all the columns from the Union query, store it, and then it might produce results (and be found through MS Query).

 

[pgraju]

RoyVidar,

That didn't work either I still got the error message.

***

Would anyone know a way of storing a queries results into a table on a scheduled basis?