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 |
#1
|
|||
|
|||
Top 3 by Sum of Value
I am trying to create a query in Access 2003
that will give me the top 3 scrap parts by value and I am getting stumped. I am trying to build it as a sub-query.... SELECT ScrapData.Value, ScrapData.PartNo, ScrapData.Description ScrapData.Date FROM ScrapData WHERE ScrapData.Value IN (SELECT TOP 3 Sum(ScrapData.Value) AS SumOfValue FROM ScrapData as Dupe WHERE Dupe.PartNo = ScrapData.PartNo); and I am getting 55 random records, not my top 3 by sum....can someone please point me in the right direction? |
#2
|
|||
|
|||
Top 3 by Sum of Value
Well you seem to be getting records where the SUM of the Value is equal to the
Value of the part no. That would indicate you are getting records where there is only one record for PartNo. If you want all the records for the top 3 parts by value. SELECT ScrapData.Value , ScrapData.PartNo , ScrapData.Description , ScrapData.Date FROM ScrapData WHERE ScrapData.PartNo IN (SELECT TOP 3 Dupe.PartNo FROM ScrapData as Dupe GROUP BY PartNO ORDER BY Sum(Value) Desc) If you want all records for the most expensive parts SELECT ScrapData.Value , ScrapData.PartNo , ScrapData.Description , ScrapData.Date FROM ScrapData WHERE ScrapData.PartNo IN (SELECT TOP 3 Dupe.PartNo FROM ScrapData as Dupe ORDER BY Value Desc) If you want something else try to describe what you do want in more detail. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Opal wrote: I am trying to create a query in Access 2003 that will give me the top 3 scrap parts by value and I am getting stumped. I am trying to build it as a sub-query.... SELECT ScrapData.Value, ScrapData.PartNo, ScrapData.Description ScrapData.Date FROM ScrapData WHERE ScrapData.Value IN (SELECT TOP 3 Sum(ScrapData.Value) AS SumOfValue FROM ScrapData as Dupe WHERE Dupe.PartNo = ScrapData.PartNo); and I am getting 55 random records, not my top 3 by sum....can someone please point me in the right direction? |
#3
|
|||
|
|||
Top 3 by Sum of Value
So this is what I did. I created one query to collect
all scrap parts by date range selected: SELECT ScrapData.ScrapDate, ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr, ScrapData.Value FROM ScrapData WHERE (((ScrapData.ScrapDate) Between [Forms]![frmWeeklyrpt]! [FromDate] And [Forms]![frmWeeklyrpt]![ToDate])); took that query (qryPart1) And created the following to give my top 3: SELECT TOP 3 qryPart1.PartNo, qryPart1.Description, Sum (qryPart1.Value) AS SumOfValue FROM qryPart1 GROUP BY qryPart1.PartNo, qryPart1.Description ORDER BY Sum(qryPart1.Value) DESC; |
#4
|
|||
|
|||
Top 3 by Sum of Value
So this is what I did. I created one query to collect
all scrap parts by date range selected: SELECT ScrapData.ScrapDate, ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr, ScrapData.Value FROM ScrapData WHERE (((ScrapData.ScrapDate) Between [Forms]![frmWeeklyrpt]! [FromDate] And [Forms]![frmWeeklyrpt]![ToDate])); took that query (qryPart1) And created the following to give my top 3: SELECT TOP 3 qryPart1.PartNo, qryPart1.Description, Sum (qryPart1.Value) AS SumOfValue FROM qryPart1 GROUP BY qryPart1.PartNo, qryPart1.Description ORDER BY Sum(qryPart1.Value) DESC; |
#5
|
|||
|
|||
Top 3 by Sum of Value
So this is what I did. I created one query to collect
all scrap parts by date range selected: SELECT ScrapData.ScrapDate, ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr, ScrapData.Value FROM ScrapData WHERE (((ScrapData.ScrapDate) Between [Forms]![frmWeeklyrpt]! [FromDate] And [Forms]![frmWeeklyrpt]![ToDate])); took that query (qryPart1) And created the following to give my top 3: SELECT TOP 3 qryPart1.PartNo, qryPart1.Description, Sum (qryPart1.Value) AS SumOfValue FROM qryPart1 GROUP BY qryPart1.PartNo, qryPart1.Description ORDER BY Sum(qryPart1.Value) DESC; |
#6
|
|||
|
|||
Top 3 by Sum of Value
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.doc" which illustrates how to do this. You can download it for free he http://www.rogersaccesslibrary.com/f...ts.asp?TID=233 -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Opal" wrote in message ... I am trying to create a query in Access 2003 that will give me the top 3 scrap parts by value and I am getting stumped. I am trying to build it as a sub-query.... SELECT ScrapData.Value, ScrapData.PartNo, ScrapData.Description ScrapData.Date FROM ScrapData WHERE ScrapData.Value IN (SELECT TOP 3 Sum(ScrapData.Value) AS SumOfValue FROM ScrapData as Dupe WHERE Dupe.PartNo = ScrapData.PartNo); and I am getting 55 random records, not my top 3 by sum....can someone please point me in the right direction? |
#7
|
|||
|
|||
Top 3 by Sum of Value
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.doc" which illustrates how to do this. You can download it for free he http://www.rogersaccesslibrary.com/f...ts.asp?TID=233 -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Opal" wrote in message ... I am trying to create a query in Access 2003 that will give me the top 3 scrap parts by value and I am getting stumped. I am trying to build it as a sub-query.... SELECT ScrapData.Value, ScrapData.PartNo, ScrapData.Description ScrapData.Date FROM ScrapData WHERE ScrapData.Value IN (SELECT TOP 3 Sum(ScrapData.Value) AS SumOfValue FROM ScrapData as Dupe WHERE Dupe.PartNo = ScrapData.PartNo); and I am getting 55 random records, not my top 3 by sum....can someone please point me in the right direction? |
#8
|
|||
|
|||
Top 3 by Sum of Value
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.doc" which illustrates how to do this. You can download it for free he http://www.rogersaccesslibrary.com/f...ts.asp?TID=233 -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Opal" wrote in message ... I am trying to create a query in Access 2003 that will give me the top 3 scrap parts by value and I am getting stumped. I am trying to build it as a sub-query.... SELECT ScrapData.Value, ScrapData.PartNo, ScrapData.Description ScrapData.Date FROM ScrapData WHERE ScrapData.Value IN (SELECT TOP 3 Sum(ScrapData.Value) AS SumOfValue FROM ScrapData as Dupe WHERE Dupe.PartNo = ScrapData.PartNo); and I am getting 55 random records, not my top 3 by sum....can someone please point me in the right direction? |
Thread Tools | |
Display Modes | |
|
|