Query for top 60 margin dollars 2

[cimoli]

I don't think i have this correct. In a query, i am trying to find the top 60 2011 MARGIN DOLLARS in descending order. And also show the other years next to it. Plus I show sales dollars and margin percents too.

I get 60 records to show up. But i don't see how the query knows the basis for the 60 records which is the highest top 2011 margin dollars.

Can you advise me? Thanks much. Cimoli


SELECT TOP 60 tblAccount.AffiliateID, tblAffiliate.AffiliateName, tblAffiliate.AffiliateClassID, Sum(IIf(Year([InvoiceDate])="2008",nz([Sales]),0)) AS [2008 Sales$], Sum(IIf(Year([InvoiceDate])="2009",nz([Sales]),0)) AS [2009 Sales$], Sum(IIf(Year([InvoiceDate])="2010",nz([Sales]),0)) AS [2010 Sales$], Sum(IIf(Year([InvoiceDate])="2011",nz([Sales]),0)) AS [2011 Sales$], " " AS X22, Sum(IIf(Year([InvoiceDate])="2008",nz([Margin]),0)) AS [2008 Margin$], Sum(IIf(Year([InvoiceDate])="2009",nz([Margin]),0)) AS [2009 Margin$], Sum(IIf(Year([InvoiceDate])="2010",nz([Margin]),0)) AS [2010 Margin$], Sum(IIf(Year([InvoiceDate])="2011",nz([Margin]),0)) AS [2011 Margin$], " " AS X33, Round(IIf(nz([2008 Sales$])=0 Or nz([2008 Margin$])=0,0,(nz([2008 Margin$])/nz([2008 Sales$]))*100),1) AS [2008 Margin%], Round(IIf(nz([2009 Sales$])=0 Or nz([2009 Margin$])=0,0,(nz([2009 Margin$])/nz([2009 Sales$]))*100),1) AS [2009 Margin%], Round(IIf(nz([2010 Sales$])=0 Or nz([2010 Margin$])=0,0,(nz([2010 Margin$])/nz([2010 Sales$]))*100),1) AS [2010 Margin%], Round(IIf(nz([2011 Sales$])=0 Or nz([2011 Margin$])=0,0,(nz([2011 Margin$])/nz([2011 Sales$]))*100),1) AS [2011 Margin%], tblAccount.AccountActive
FROM tblAffiliate RIGHT JOIN (tblSalesTemp LEFT JOIN tblAccount ON tblSalesTemp.CusID = tblAccount.CusID) ON tblAffiliate.AffiliateID = tblAccount.AffiliateID
GROUP BY tblAccount.AffiliateID, tblAffiliate.AffiliateName, tblAffiliate.AffiliateClassID, " ", " ", tblAccount.AccountActive
HAVING (((tblAccount.AccountActive)="Y"));

 

[PWise]

add an order by

order by [2011 Margin$]

 

[misscrf]

add a sort (order by) for your preference of priority of the top 60.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[cimoli]

Thanks to both of you. Did i do this right?
In the qry design, i right clicked in the top area, went down to ORDER BY and put this in ....
Qry8254HighestMarginAffiliate.[2011 Margin$] DESC

cimoli

 

[misscrf]

sounds right. If you sort the table with that value descending, can you see the top 60 results when compared to your query?

misscrf

It is never too late to become what you could have been ~ George Eliot