Excel says too few parameters query is ok in Acces

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Excel says too few parameters query is ok in Acces

Author

Topic

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-11-30 : 18:55:11

Hi,
I posted a message earlier and decieded to just use a pivot table in Excell now.
Here the trouble.
Other queries read fine into my excel file but when I made a query that queries a union table i get the error:

"Microsoft Query, too few parameters, expected 1"

why? its just a simple select query i am calling thru the "get external data feature"

SELECT [YEAR], Format(DateSerial(2000,[MONTH],1),"mmm") AS EVENT_MONTH, [EVENTS], [LOCATION]
FROM [union events by location]
ORDER BY Year, Month, Location;

It is querying a unioned query that if I call thru Excel gives me the same grief.

here it is:

SELECT Year([DATE]) AS [YEAR], Month([DATE]) AS [MONTH], Count([encounter]) AS Events, [location]
FROM Event_table
WHERE [Location] IN ('room1','room2','cafe')
GROUP BY Year([DATE]), Month([DATE]), [Location]
UNION
SELECT Year([DATE]) AS [YEAR], Month([DATE]) AS [MONTH], Count([encounter]) AS Events, "OTHER" AS Location
FROM Falls
WHERE [Location] NOT IN ('room1','room2','cafe')
GROUP BY Year([DATE]), Month([DATE]), [Location];

they both work fine in access.

help! = (

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-30 : 20:14:29

i think this is your problem:

SELECT Year([DATE]) AS [YEAR], Month([DATE]) AS [MONTH], Count([encounter]) AS Events, [location]
FROM Event_table
WHERE [Location] IN ('room1','room2','cafe')
GROUP BY Year([DATE]), Month([DATE]), [Location]
UNION
SELECT Year([DATE]) AS [YEAR], Month([DATE]) AS [MONTH], Count([encounter]) AS Events, "OTHER" AS Location
FROM Falls
WHERE [Location] NOT IN ('room1','room2','cafe')
GROUP BY Year([DATE]), Month([DATE]), [Location];

trying getting rid of "location" in that group by. of course, we have nowhere near enough info to know exactly what is wrong. A parameter is created if you try to reference a field that doesn't exist in your tables -- and you have not told us what fields are in the tables in this query, so there's really no way we can know for sure. Makes sense?

- Jeff

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-12-01 : 17:45:16

Thanks. Sure enough, when I take location out all together I don't get the Param error in Excel.

You see the Union of 2 queries: I am doing this because I want to group a bunch of less important locations as "Other" in my Pivot table.

I've tried a few syntax changes and figure out how to make this work.
It works as posted in Access but gives the Param error in Excel.

Any idea why this is?

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2004-12-01 : 17:50:25

Ahhh! Crap.
Got it.

'Other' AS [Location]
not
"Other" AS [Location]

Thanks a ton!
Some of you, just for being there to listen. = )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources