Re: $$Excel-Macros$$ RecordSet ADO get the 20th record
Sorry for my Fault, I translate PAIS in portuguese to country and this can mess your thoughts but nevertheless you were precise! I adapt just the name and works beutifully! I am thankful for your help. Follow the answer. SELECT TOP 20 MIDASQ12013.PAIS AS Expr1, MIDASQ12013.ATC3_COD, Sum(MIDASQ12013.EUR_2008) AS KEUR08, Sum(MIDASQ12013.EUR_2009) AS KEUR09, Sum(MIDASQ12013.EUR_2010) AS KEUR10, Sum(MIDASQ12013.EUR_2011) AS KEUR11, Sum(MIDASQ12013.EUR_2012) AS KEUR12, Sum(MIDASQ12013.EUR_2013) AS KEUR13, Sum(MIDASQ12013.UN_2008) AS KUN08, Sum(MIDASQ12013.UN_2009) AS KUN09, Sum(MIDASQ12013.UN_2010) AS KUN10, Sum(MIDASQ12013.UN_2011) AS KUN11, Sum(MIDASQ12013.UN_2012) AS KUN12, Sum(MIDASQ12013.UN_2013) AS KUN13 FROM MIDASQ12013 GROUP BY MIDASQ12013.ATC3_COD, MIDASQ12013.PAIS HAVING (((MIDASQ12013.PAIS)="brazil retail")) ORDER BY Sum(MIDASQ12013.EUR_2013) DESC; Regards, *Michael* Em sexta-feira, 20 de setembro de 2013 10h38min41s UTC-3, Enrique Martin escreveu: > > Use below one there is no field named as country.. if it doesn't > work then let me knw. > > SELECT TOP 20 MIDASQ12013.COUNTRY, MIDASQ12013.ATC3_COD, > Sum(MIDASQ12013.EUR_2008) AS KEUR08, Sum(MIDASQ12013.EUR_2009) AS KEUR09, > Sum(MIDASQ12013.EUR_2010) AS KEUR10, Sum(MIDASQ12013.EUR_2011) AS KEUR11, > Sum(MIDASQ12013.EUR_2012) AS KEUR12, Sum(MIDASQ12013.EUR_2013) AS KEUR13, > Sum(MIDASQ12013.UN_2008) AS KUN08, Sum(MIDASQ12013.UN_2009) AS KUN09, > Sum(MIDASQ12013.UN_2010) AS KUN10, Sum(MIDASQ12013.UN_2011) AS KUN11, > Sum(MIDASQ12013.UN_2012) AS KUN12, Sum(MIDASQ12013.UN_2013) AS KUN13 > FROM MIDASQ12013 > GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD > HAVING (((MIDASQ12013.PAIS)="brazil retail")) > ORDER BY Sum(MIDASQ12013.EUR_2013) DESC > > > On Fri, Sep 20, 2013 at 5:09 PM, Michael Pablo > > > wrote: > >> Sure! follow some data of my database. >> >> Em sexta-feira, 20 de setembro de 2013 01h57min07s UTC-3, Enrique Martin >> escreveu: >>> >>> can you share your access database? >>> >>> >>> On Thu, Sep 19, 2013 at 10:40 PM, Michael Pablo wrote: >>> >>>> Hi Enrique, >>>> >>>> I've attempted this Query but I didn't get results. They return this >>>> advertence, saying that I need to use the clausule EXISTS. >>>> >>>> SELECT TOP 1 * FROM MIDASQ12013 >>>> WHERE MIDASQ12013.PAIS EXISTS (SELECT TOP 20 MIDASQ12013.PAIS, >>>> MIDASQ12013.ATC3_COD, Sum(MIDASQ12013.EUR_2008) AS KEUR08, >>>> Sum(MIDASQ12013.EUR_2009) AS KEUR09, Sum(MIDASQ12013.EUR_2010) AS KEUR10, >>>> Sum(MIDASQ12013.EUR_2011) AS KEUR11, Sum(MIDASQ12013.EUR_2012) AS KEUR12, >>>> Sum(MIDASQ12013.EUR_2013) AS KEUR13, Sum(MIDASQ12013.UN_2008) AS KUN08, >>>> Sum(MIDASQ12013.UN_2009) AS KUN09, Sum(MIDASQ12013.UN_2010) AS KUN10, >>>> Sum(MIDASQ12013.UN_2011) AS KUN11, Sum(MIDASQ12013.UN_2012) AS KUN12, >>>> Sum(MIDASQ12013.UN_2013) AS KUN13 >>>> FROM MIDASQ12013 >>>> GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD >>>> HAVING (((MIDASQ12013.PAIS)="BRAZIL RETAIL")) >>>> ORDER BY Sum(MIDASQ12013.EUR_2013)) >>>> ORDER BY MIDASQ12013.COUNTRY DESC; >>>> >>>> Thanks, >>>> >>>> *Michael* >>>> >>>> >>>> >>>> Em quinta-feira, 19 de setembro de 2013 00h10min28s UTC-3, Enrique >>>> Martin escreveu: >>>>> >>>>> I hope the below one works good to you... pls check >>>>> >>>>> SELECT TOP 1 * FROM MIDASQ12013 >>>>> WHERE MIDASQ12013.COUNTRY IN (SELECT TOP 20 MIDASQ12013.COUNTRY, >>>>> MIDASQ12013.ATC3_COD, Sum(MIDASQ12013.EUR_2008) AS KEUR08, >>>>> Sum(MIDASQ12013.EUR_2009) AS KEUR09, Sum(MIDASQ12013.EUR_2010) AS KEUR10, >>>>> Sum(MIDASQ12013.EUR_2011) AS KEUR11, Sum(MIDASQ12013.EUR_2012) AS KEUR12, >>>>> Sum(MIDASQ12013.EUR_2013) AS KEUR13, Sum(MIDASQ12013.UN_2008) AS KUN08, >>>>> Sum(MIDASQ12013.UN_2009) AS KUN09, Sum(MIDASQ12013.UN_2010) AS KUN10, >>>>> Sum(MIDASQ12013.UN_2011) AS KUN11, Sum(MIDASQ12013.UN_2012) AS KUN12, >>>>> Sum(MIDASQ12013.UN_2013) AS KUN13 >>>>> FROM MIDASQ12013 >>>>> GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD >>>>> HAVING (((MIDASQ12013.PAIS)="brazil retail")) >>>>> ORDER BY Sum(MIDASQ12013.EUR_2013)) >>>>> ORDER BY MIDASQ12013.COUNTRY DESC; >>>>> >>>>> >>>>> On Thu, Sep 19, 2013 at 12:12 AM, Michael Pablo wr
Re: $$Excel-Macros$$ RecordSet ADO get the 20th record
Hi Enrique, I've attempted this Query but I didn't get results. They return this advertence, saying that I need to use the clausule EXISTS. SELECT TOP 1 * FROM MIDASQ12013 WHERE MIDASQ12013.PAIS EXISTS (SELECT TOP 20 MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD, Sum(MIDASQ12013.EUR_2008) AS KEUR08, Sum(MIDASQ12013.EUR_2009) AS KEUR09, Sum(MIDASQ12013.EUR_2010) AS KEUR10, Sum(MIDASQ12013.EUR_2011) AS KEUR11, Sum(MIDASQ12013.EUR_2012) AS KEUR12, Sum(MIDASQ12013.EUR_2013) AS KEUR13, Sum(MIDASQ12013.UN_2008) AS KUN08, Sum(MIDASQ12013.UN_2009) AS KUN09, Sum(MIDASQ12013.UN_2010) AS KUN10, Sum(MIDASQ12013.UN_2011) AS KUN11, Sum(MIDASQ12013.UN_2012) AS KUN12, Sum(MIDASQ12013.UN_2013) AS KUN13 FROM MIDASQ12013 GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD HAVING (((MIDASQ12013.PAIS)="BRAZIL RETAIL")) ORDER BY Sum(MIDASQ12013.EUR_2013)) ORDER BY MIDASQ12013.COUNTRY DESC; Thanks, *Michael* Em quinta-feira, 19 de setembro de 2013 00h10min28s UTC-3, Enrique Martin escreveu: > > I hope the below one works good to you... pls check > > SELECT TOP 1 * FROM MIDASQ12013 > WHERE MIDASQ12013.COUNTRY IN (SELECT TOP 20 MIDASQ12013.COUNTRY, > MIDASQ12013.ATC3_COD, Sum(MIDASQ12013.EUR_2008) AS KEUR08, > Sum(MIDASQ12013.EUR_2009) AS KEUR09, Sum(MIDASQ12013.EUR_2010) AS KEUR10, > Sum(MIDASQ12013.EUR_2011) AS KEUR11, Sum(MIDASQ12013.EUR_2012) AS KEUR12, > Sum(MIDASQ12013.EUR_2013) AS KEUR13, Sum(MIDASQ12013.UN_2008) AS KUN08, > Sum(MIDASQ12013.UN_2009) AS KUN09, Sum(MIDASQ12013.UN_2010) AS KUN10, > Sum(MIDASQ12013.UN_2011) AS KUN11, Sum(MIDASQ12013.UN_2012) AS KUN12, > Sum(MIDASQ12013.UN_2013) AS KUN13 > FROM MIDASQ12013 > GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD > HAVING (((MIDASQ12013.PAIS)="brazil retail")) > ORDER BY Sum(MIDASQ12013.EUR_2013)) > ORDER BY MIDASQ12013.COUNTRY DESC; > > > On Thu, Sep 19, 2013 at 12:12 AM, Michael Pablo > > > wrote: > >> Hello Enrique Martin, >> >> I have been attempted this query: >> >> SELECT MIDASQ12013.COUNTRY, MIDASQ12013.ATC3_COD, >> Sum(MIDASQ12013.EUR_2008) AS KEUR08, Sum(MIDASQ12013.EUR_2009) AS KEUR09, >> Sum(MIDASQ12013.EUR_2010) AS KEUR10, Sum(MIDASQ12013.EUR_2011) AS KEUR11, >> Sum(MIDASQ12013.EUR_2012) AS KEUR12, Sum(MIDASQ12013.EUR_2013) AS KEUR13, >> Sum(MIDASQ12013.UN_2008) AS KUN08, Sum(MIDASQ12013.UN_2009) AS KUN09, >> Sum(MIDASQ12013.UN_2010) AS KUN10, Sum(MIDASQ12013.UN_2011) AS KUN11, >> Sum(MIDASQ12013.UN_2012) AS KUN12, Sum(MIDASQ12013.UN_2013) AS KUN13 >> FROM MIDASQ12013 >> GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD >> HAVING (((MIDASQ12013.PAIS)="brazil retail")) >> ORDER BY Sum(MIDASQ12013.EUR_2013) DESC >> >> Ok, I am not experienced about ADO or DAO and how to utilize one or >> other. But in this case ADO can account this query? Because maybe this can >> be a barrier about execute this query in my VBA code. >> >> Thanks and Regards, >> >> *Michael* >> >> Em segunda-feira, 16 de setembro de 2013 22h44min29s UTC-3, Enrique >> Martin escreveu: >>> >>> Make a query as "Select Top 20 From " in case you need >>> a 20th record without changing your records order. >>> >>> but if you want the 20th record after sorting your data in ascending >>> order then make following query "Select Top 20 From order >>> by " >>> >>> In case you need the 20th record after sorting data in descending order >>> then use the following one "Select Top 20 From order by >>> DESC" >>> >>> In case you want to call any of these queries by ADO then create >>> queryDef object. >>> >>> >>> On Mon, Sep 16, 2013 at 7:33 PM, Michael Pablo wrote: >>> >>>> >>>> >>>> Hello Everbody! >>>> >>>> I would like to ask help with this duty, I don't Know how to get the >>>> 20th records from a record set. I tried with the While Clausule but I fail. >>>> >>>> And I don't Know if you can help me about that too because it is more a >>>> doubt SQL, where I divide by 1000 the registers but I aways get a Error, I >>>> don't Know Why because in access the Query works perfectly. >>>> >>>> Best Regards, >>>> >>>> *Michael* >>>> >>>> -- >>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>> https://www.facebook.com/**discussexcel<https://www.facebook.com/discussexcel> >&
Re: $$Excel-Macros$$ RecordSet ADO get the 20th record
Hello Enrique Martin, I have been attempted this query: SELECT MIDASQ12013.COUNTRY, MIDASQ12013.ATC3_COD, Sum(MIDASQ12013.EUR_2008) AS KEUR08, Sum(MIDASQ12013.EUR_2009) AS KEUR09, Sum(MIDASQ12013.EUR_2010) AS KEUR10, Sum(MIDASQ12013.EUR_2011) AS KEUR11, Sum(MIDASQ12013.EUR_2012) AS KEUR12, Sum(MIDASQ12013.EUR_2013) AS KEUR13, Sum(MIDASQ12013.UN_2008) AS KUN08, Sum(MIDASQ12013.UN_2009) AS KUN09, Sum(MIDASQ12013.UN_2010) AS KUN10, Sum(MIDASQ12013.UN_2011) AS KUN11, Sum(MIDASQ12013.UN_2012) AS KUN12, Sum(MIDASQ12013.UN_2013) AS KUN13 FROM MIDASQ12013 GROUP BY MIDASQ12013.PAIS, MIDASQ12013.ATC3_COD HAVING (((MIDASQ12013.PAIS)="brazil retail")) ORDER BY Sum(MIDASQ12013.EUR_2013) DESC Ok, I am not experienced about ADO or DAO and how to utilize one or other. But in this case ADO can account this query? Because maybe this can be a barrier about execute this query in my VBA code. Thanks and Regards, *Michael* Em segunda-feira, 16 de setembro de 2013 22h44min29s UTC-3, Enrique Martin escreveu: > > Make a query as "Select Top 20 From " in case you need a > 20th record without changing your records order. > > but if you want the 20th record after sorting your data in ascending order > then make following query "Select Top 20 From order by > " > > In case you need the 20th record after sorting data in descending order > then use the following one "Select Top 20 From order by > DESC" > > In case you want to call any of these queries by ADO then create queryDef > object. > > > On Mon, Sep 16, 2013 at 7:33 PM, Michael Pablo > > > wrote: > >> >> >> Hello Everbody! >> >> I would like to ask help with this duty, I don't Know how to get the >> 20th records from a record set. I tried with the While Clausule but I fail. >> >> And I don't Know if you can help me about that too because it is more a >> doubt SQL, where I divide by 1000 the registers but I aways get a Error, I >> don't Know Why because in access the Query works perfectly. >> >> Best Regards, >> >> *Michael* >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> 2) Don't post a question in the thread of another member. >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> 4) Acknowledge the responses you receive, good or bad. >> 5) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros...@googlegroups.com . >> To post to this group, send email to excel-...@googlegroups.com >> . >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > > > -- > Regards, > Anoop > Sr. Developer > Facebook ID - https://www.facebook.com/anooop.k.sharma > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
