Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Complicated Access Query into Excel
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")); |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Complicated Access Query into Excel
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 om... 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")); |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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")); . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Complicated Access Query into Excel
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
Importing access query | Excel Discussion (Misc queries) | |||
Importing Access Query | Excel Discussion (Misc queries) | |||
Problems importing from an Access query | Excel Discussion (Misc queries) | |||
Importing a table from Access query | Excel Discussion (Misc queries) |