Microsoft Query

[MountainHawk]

Is there a way to get MS Query to bring data from Access to Excel based ona query that has both a parameter, and summary functions?

Essentially, I have a table in Access with:

A B C D E F Data1 Data2

And I want to sum out the F column to get in Excel:

A B C D E Sum(Data1) Sum(Data2)

where there is only one line for each A B C D E combo.

I know I can run a make table query in Access then run a normal parameter query from MS Query to do what I want, but I'm just seeing if there is a way to avoid the extra step.

Thanks.

[TwistedMentat]

If someone tells you a way, then I will kick myself. I have always passed parameters using VBA.

[MountainHawk]

You can definately pass parameters thru Excel to query. You just can't seem to use WHERE and GROUP BY in the same SQL statement without Query giving an error.

[whisper]

Why don't you read the Access Table directly into a Pivot Table?

[Arlie_Proctor]

Twisted is correct and you can kick both of us if you can get MSQuery to operate reliably in the manner you describe. I wouldn't waste my time if I were you. You need or will soon need VBA.

If your data source is Access, use DAO. If not, use ADO. The initial learning curve is steep, but from there you can be much more flexible with your data acquisition techniques.

[Brad Gile]

Twisted is correct and you can kick both of us if you can get MSQuery to operate reliably in the manner you describe. I wouldn't waste my time if I were you. You need or will soon need VBA.

If your data source is Access, use DAO. If not, use ADO. The initial learning curve is steep, but from there you can be much more flexible with your data acquisition techniques.

I'll second that. If you want to get a handle on DAO, try Simon Tushingham's primer:
http://www.vb-faq.com/Articles/Tushingham/DAO_Primer1.asp

It's an old article (as is DAO!), but excellent. While you are at it, take a look around vb-faq; it has a wealth of information on vb (and, by extension, vba).

Disclosure: I am a vb-faq contributor, but receive no compensation for it.

Brad