Importing Complicated Access Query into Excel

M

Mike Roberto

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

RB Smissaert

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
 
F

Frank stone

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 said:
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"));
Click to expand...

.
Click to expand...
 
M

Mike Roberto

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"));
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Processing Text Data for importing into Excel 1
creating a query from two tables 2
Only want to see first record 1
I need to display info from two different table into one query 12
WinSQL Query - Syntax for use in Excel 3
Refer to a query in qry of rpt 1
DATABASE REPORT/CROSSTAB QUERY HELP 2
Counting Query 2

Top