Nested Query Problem or Grouping Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: mike (anonymous_at_discussions.microsoft.com)
Date: 05/04/04 

Date: Tue, 4 May 2004 12:02:13 -0700

Hi. Any suggestions for this would be great!!!! It used to
work fine so I'm not sure what I did wrong. I have a view
that shown below (sorry for the length) that counts the
number of days from when an account was trained to when it
ordered, and the time between orders. The problem is that
I'm seeing everything in triplicate now. It looks like
it's not grouping the lines. Any thoughts?

AccountNumber AccountName CourseDate
CourseLocation InvoiceDate Sales
DaysElapsedFromTrainingToOrder,
DaysElapsedBetweenOrders SalesKits UnitsKits

10003 AccountName 4/11/2003
Dallas 7/31/2003 5315

111 5085 3

10003 AccountName 4/11/2003
Dallas 7/31/2003 5315

111 5085 3

10003 AccountName 4/11/2003
Dallas 7/31/2003 5315

111 5085 3

10003 AccountName 4/11/2003
Dallas 8/18/2003 3390
129
18 3390 2

10003 AccountName 4/11/2003
Dallas 8/18/2003 3390
129
18 3390 2

10003 AccountName 4/11/2003
Dallas 8/18/2003 3390
129
18 3390 2

10003 AccountName 4/11/2003
Dallas 1/20/2004 9050
284
155 8475 5

10003 AccountName 4/11/2003
Dallas 1/20/2004 9050
284
155 8475 5

10003 AccountName 4/11/2003
Dallas 1/20/2004 9050
284
155 8475 5

SELECT TOP 100 PERCENT
dbo.qryTimeBetweenOrders.AccountNumber,
dbo.tblAccountList.CUSTOMER_N AS AccountName,
                      
dbo.qryCourseLocationAccountsTrainedFirstTime.CourseDate,
dbo.qryCourseLocationAccountsTrainedFirstTime.CourseLocatio
n,
                      
dbo.qryTimeBetweenOrders.InvoiceDate,
dbo.qryTimeBetweenOrders.Sales, DATEDIFF(dd,
                      
dbo.qryCourseLocationAccountsTrainedFirstTime.CourseDate,
dbo.qryTimeBetweenOrders.InvoiceDate) AS
DaysElapsedFromTrainingToOrder,
                      
dbo.qryTimeBetweenOrders.DaysElapsedBetweenOrders,
dbo.qryTimeBetweenOrders.SalesKits,
dbo.qryTimeBetweenOrders.UnitsKits
FROM dbo.qryTimeBetweenOrders LEFT OUTER JOIN
                      
dbo.qryCourseLocationAccountsTrainedFirstTime ON
                      
dbo.qryTimeBetweenOrders.AccountNumber =
dbo.qryCourseLocationAccountsTrainedFirstTime.SAPNumber
LEFT OUTER JOIN
                      dbo.tblAccountList ON
dbo.qryTimeBetweenOrders.AccountNumber =
dbo.tblAccountList.CUSTOMER__
WHERE (dbo.qryTimeBetweenOrders.InvoiceDate < CONVERT
(DATETIME, '2004-05-01 00:00:00', 102))
ORDER BY dbo.qryTimeBetweenOrders.AccountNumber,
dbo.qryTimeBetweenOrders.InvoiceDate