Home |
Search |
Today's Posts |
#11
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Access Query Linked to Excel fixed rows | Links and Linking in Excel | |||
Link Access table to Excel worksheet | Excel Discussion (Misc queries) | |||
link Access workbook to Excel workbook | Excel Discussion (Misc queries) | |||
Trying to link MS Access to MS Excel | Links and Linking in Excel | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) |