Importing Complicated Access Query into Excel
your error "too few parameters - expecting 2" is due to
the fact that your prompts for parameters are in the
access query. you will have to remove these and place the
prompts for parameters on the excel side.
-----Original Message-----
Where will the data to work with be, in Access or Excel?
If in Access your best bet might be to use OLE automation
and run
Acces from Excel.
RBS
"Mike Roberto" wrote in message
. com...
Hi everyone, I've been searching but can't find a
solution to this
problem.
I have a linked access database that has a query which
works
perfectly. It asks the user the daterange to use, and
also performs a
few calculations.
I want to get this exact Query into Excel, but the MS
Query module
from Get External Data is VERY simplistic and can't get
the data in.
What's the best plan of attack to get this data into
excel
automatically?
Creating the Access MDB as an ODBC driver didn't help -
I try to
import the query and get an error (Too Few Parameters -
Expecting 2),
and I want it to ask the user the daterange anyway. Is
VBA my best
option? I know some VBA, but not with the database
stuff.
Any suggestions or documents would be greatly
appreciated. In case it
helps, I'll paste the ACCESS SQL code below. Maybe
someone can show
how to convert it into MS Query SQL code format.
Thanks!!
mike
SQL from Access:
SELECT [dbo_QTZ_US MFG PRODUCTION DETAIL].[Effective
Date],
[dbo_QTZ_US MFG PRODUCTION DETAIL].[Work Center],
[dbo_QTZ_US MFG
PRODUCTION DETAIL].[Item Number], [dbo_QTZ_US ITEM
MASTER].Description1, [dbo_QTZ_US MFG PRODUCTION
DETAIL].[Act Run
Time], [dbo_QTZ_US MFG PRODUCTION DETAIL].[Std Run
Time], [dbo_QTZ_US
MFG PRODUCTION DETAIL].[Qty Completed], [Qty Completed]*
[Alternate UM
Conversion] AS [Lbs Pass], [Qty Completed]+[Qty Reject]
AS [Qty Made],
[Qty Made]*[Alternate UM Conversion] AS [Lbs Made], [Qty
Completed]/[Qty Made] AS Yield, [dbo_QTZ_US EMPLOYEE
MASTER].[Last
Name], [dbo_QTZ_US MFG PRODUCTION DETAIL].Site
FROM (([dbo_QTZ_US MFG PRODUCTION DETAIL] INNER JOIN
[dbo_QTZ_US ITEM
MASTER] ON [dbo_QTZ_US MFG PRODUCTION DETAIL].[Item
Number] =
[dbo_QTZ_US ITEM MASTER].[Item Number]) INNER JOIN
[dbo_QTZ_US ITEM
MASTER EXTENDED] ON [dbo_QTZ_US MFG PRODUCTION DETAIL].
[Item Number] =
[dbo_QTZ_US ITEM MASTER EXTENDED].[Item Number]) INNER
JOIN
[dbo_QTZ_US EMPLOYEE MASTER] ON [dbo_QTZ_US MFG
PRODUCTION
DETAIL].Employee = [dbo_QTZ_US EMPLOYEE MASTER].Employee
WHERE ((([dbo_QTZ_US MFG PRODUCTION DETAIL].[Effective
Date])=[Start
Date?] And ([dbo_QTZ_US MFG PRODUCTION DETAIL].
[Effective Date])<=[End
Date?]) AND ((Left([Work Center],3))="FIN"));
.
|