Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old October 15th 05, 02:06 PM
jwr
 
Posts: n/a
Default

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   Report Post  
Old October 15th 05, 05:25 PM
Bill Manville
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Query Linked to Excel fixed rows Chris Edmunds Links and Linking in Excel 3 September 3rd 05 07:42 AM
Link Access table to Excel worksheet dalesrunner Excel Discussion (Misc queries) 1 March 28th 05 12:09 AM
link Access workbook to Excel workbook Toinett Excel Discussion (Misc queries) 1 January 31st 05 03:37 PM
Trying to link MS Access to MS Excel booris Links and Linking in Excel 1 January 20th 05 06:12 PM
Query a Access database that has a module from Excel Oggie Excel Discussion (Misc queries) 1 January 4th 05 08:43 AM


All times are GMT +1. The time now is 03:27 AM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017