ExcelBanter - View Single Post - Importing Complicated Access Query into Excel

Mike Roberto

(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"));

I got this query to work in Excel quickly after. It turns out that
Excel's MS Query (Office 2000) uses a different format than access
queries. Why, i have no clue. After converting the query, I killed
the automatic yield process since there was a divide by zero error.

Anyway, here's what the built-in query ended up looking like after
converting it to Excel's MS Query style:

SELECT "QTZ_US MFG PRODUCTION DETAIL"."Effective Date", "QTZ_US MFG
PRODUCTION DETAIL"."Work Center", "QTZ_US MFG PRODUCTION DETAIL"."Item
Number", "QTZ_US ITEM MASTER".Description1, "QTZ_US MFG PRODUCTION
DETAIL"."Act Run Time", "QTZ_US MFG PRODUCTION DETAIL"."Std Run Time",
"QTZ_US MFG PRODUCTION DETAIL"."Qty Completed", "Qty
Completed"*"Alternate UM Conversion" AS 'Lbs Pass', "Qty
Completed"+"Qty Reject" AS 'Qty Made', ("Qty Completed"+"Qty
Reject")*"Alternate UM Conversion" AS 'Lbs Made', "QTZ_US EMPLOYEE
MASTER"."Last Name", "QTZ_US MFG PRODUCTION DETAIL".Site

FROM SQLGDB.dbo."QTZ_US EMPLOYEE MASTER" "QTZ_US EMPLOYEE MASTER",
SQLGDB.dbo."QTZ_US ITEM MASTER" "QTZ_US ITEM MASTER",
SQLGDB.dbo."QTZ_US ITEM MASTER EXTENDED" "QTZ_US ITEM MASTER
EXTENDED", SQLGDB.dbo."QTZ_US MFG PRODUCTION DETAIL" "QTZ_US MFG
PRODUCTION DETAIL"

WHERE "QTZ_US MFG PRODUCTION DETAIL".Employee = "QTZ_US EMPLOYEE
MASTER".Employee AND "QTZ_US ITEM MASTER"."Item Number" = "QTZ_US ITEM
MASTER EXTENDED"."Item Number" AND "QTZ_US MFG PRODUCTION
DETAIL"."Item Number" = "QTZ_US ITEM MASTER"."Item Number" AND
(("QTZ_US MFG PRODUCTION DETAIL"."Effective Date"=? And "QTZ_US MFG
PRODUCTION DETAIL"."Effective Date"<=?) AND (Left("Work
Center",3)='FIN'))

so if anyone has that problem, realize that you need to use quotes,
not brackets... and a few other notation changes. Pretty stupid.