If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Import query from access to excel, link to template, email on
XP Pro/Excel 2002/Microsoft Office 2003
SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry, Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress, Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem, [Order Details].Quantity, Products.ProductCode, Products.ProductName, Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice, [Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price], IIf([Total Price]=0,[Total Price],Null) AS [Amount Before Trade-In], IIf([Total Price]0,[Total Price],Null) AS [Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS [Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #], [JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice Amount]*Products!HandlingPct AS [Handling $] FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN ((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID = Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE (((Customers.ControlNum)=[forms]![ParamControl#]![Beginning ControlNum] And (Customers.ControlNum)=[forms]![ParamControl#]![Ending ControlNum])); "Bill Manville" wrote in message ... Can you post the SQL of the Access query? And confirm the version of Office that you are using? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#12
|
|||
|
|||
Import query from access to excel, link to template, email on
Quite a big query to redefine in MSQuery from scratch.
What I suggest you do is use Data / Import External Data / New Database Query to define a simple query on the same database. After returning results to Excel, right-click the results area and Edit Query. If it doesn't take you direct to the MSQuery query grid, click Next until you reach the end of the wizard and then select to edit the query in MS Query. In MS Query click the SQL button and then paste this slightly modified version of your query into the SQL window replacing what was there before. SELECT Orders.OrderDate, Contracts.ContractNum, Customers.PONumber, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.ContactTitle, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode, Orders.ShipCountry, Orders.ShipPhoneNumber, Orders.MarkForName, Orders.MarkForAddress, Orders.MarkForCountry, Orders.MarkForPostalCode, [Order Details].LineItem, [Order Details].Quantity, Products.ProductCode, Products.ProductName, Customers.ControlNum, [Order Details].SerialNum, [Order Details].UnitPrice, [Order Details]!UnitPrice*[Order Details]!Quantity AS [Total Price], IIf([Total Price]=0,[Total Price],Null) AS [Amount Before Trade-In], IIf([Total Price]0,[Total Price],Null) AS [Trade-In Amount], [Order Details].OrderID, Orders.ShipDate, ([Amount Before Trade-In]*0.95) AS [Dealere 95%], 0.05*[Amount Before Trade-In] AS [SEC 5%], Dealer.DealerName, Dealer.ContactPerson, Dealer.City, Dealer.State, Dealer.UnitCode, Dealer.DealerNum, Dealer.DealerType, [DlvDlrJDInv] AS [JD Invoice #], [JDInv$] AS [JD Invoice Amount], Products.HandlingPct, [JD Invoice Amount]*Products!HandlingPct AS [Handling $] FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Dealer.DealerID = Orders.DealerID) INNER JOIN ((Contracts INNER JOIN (SIN INNER JOIN Products ON SIN.SINID = Products.SINID) ON Contracts.ContractID = Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE Customers.ControlNum=[First Control Number?] And Customers.ControlNum=[Last Control Number?] If you now try to run the query by clicking the ! button it might flag some syntax it doesn't like - in which case give me details and I'll try to help you fix it - or it will prompt you for the 2 parameters (First Control Number and Last Control Number). File / Return results to Excel. Then when you refresh the query in Excel it should prompt you again for the parameters. As mentioned before you can link the parameters to cells if you wish. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Linking an Excel worksheet to an Access table | Sitara Lal | General Discussions | 6 | August 9th, 2004 10:13 PM |
Import Excel Into Access - Primary Key Error | Karl Burrows | General Discussion | 5 | August 5th, 2004 01:35 AM |
Add-In Link from Excel to Access | Ann Mindy | Worksheet Functions | 0 | November 16th, 2003 04:08 PM |
Excel - Import External Data from Access | VLeonard | Setting up and Configuration | 1 | November 7th, 2003 05:59 PM |