MS Access: Find the most common user per month query












0















Fairly new to Access 2016 and I'm writing a query to obtain the most common user within the database per month.



So a record would be



Table1
ID Date1
1 2019-02-28


This is my code grouping the totals per month:



Month: Format([Date1],"mmmm")
TopUser: (SELECT TOP 1 [Table1]![ID] FROM [Table1] GROUP BY
[Table1]![ID] order by COUNT([Table1]![ID]) DESC)


Expectation:



Month     TopUser
January 2
February 1
March 2
April 3


Result:



Month     TopUser
January 2
February 2
March 2
April 2


So my code is returning back the most common user overall instead of for each month. I'm not sure if this is an access aspect that I'm misinterpreting or if its my queries.










share|improve this question





























    0















    Fairly new to Access 2016 and I'm writing a query to obtain the most common user within the database per month.



    So a record would be



    Table1
    ID Date1
    1 2019-02-28


    This is my code grouping the totals per month:



    Month: Format([Date1],"mmmm")
    TopUser: (SELECT TOP 1 [Table1]![ID] FROM [Table1] GROUP BY
    [Table1]![ID] order by COUNT([Table1]![ID]) DESC)


    Expectation:



    Month     TopUser
    January 2
    February 1
    March 2
    April 3


    Result:



    Month     TopUser
    January 2
    February 2
    March 2
    April 2


    So my code is returning back the most common user overall instead of for each month. I'm not sure if this is an access aspect that I'm misinterpreting or if its my queries.










    share|improve this question



























      0












      0








      0








      Fairly new to Access 2016 and I'm writing a query to obtain the most common user within the database per month.



      So a record would be



      Table1
      ID Date1
      1 2019-02-28


      This is my code grouping the totals per month:



      Month: Format([Date1],"mmmm")
      TopUser: (SELECT TOP 1 [Table1]![ID] FROM [Table1] GROUP BY
      [Table1]![ID] order by COUNT([Table1]![ID]) DESC)


      Expectation:



      Month     TopUser
      January 2
      February 1
      March 2
      April 3


      Result:



      Month     TopUser
      January 2
      February 2
      March 2
      April 2


      So my code is returning back the most common user overall instead of for each month. I'm not sure if this is an access aspect that I'm misinterpreting or if its my queries.










      share|improve this question
















      Fairly new to Access 2016 and I'm writing a query to obtain the most common user within the database per month.



      So a record would be



      Table1
      ID Date1
      1 2019-02-28


      This is my code grouping the totals per month:



      Month: Format([Date1],"mmmm")
      TopUser: (SELECT TOP 1 [Table1]![ID] FROM [Table1] GROUP BY
      [Table1]![ID] order by COUNT([Table1]![ID]) DESC)


      Expectation:



      Month     TopUser
      January 2
      February 1
      March 2
      April 3


      Result:



      Month     TopUser
      January 2
      February 2
      March 2
      April 2


      So my code is returning back the most common user overall instead of for each month. I'm not sure if this is an access aspect that I'm misinterpreting or if its my queries.







      database ms-access-2016






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 26 '18 at 23:37









      Strawberry

      26.3k83149




      26.3k83149










      asked Nov 26 '18 at 22:29









      jamieDoejamieDoe

      1




      1
























          1 Answer
          1






          active

          oldest

          votes


















          0














          Try filtering on the month:



          Select 
          Format([Date1], "yyyymm") As YearMonth,
          (Select Top 1 T.ID
          From Table1 As T
          Where Format(T.[Date1], "yyyymm") = Format(Table1.[Date1], "yyyymm")
          Group By ID
          Order By Count(T.ID) Desc) As TopID
          From
          Table1
          Group By
          Format([Date1], "yyyymm")





          share|improve this answer


























          • Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

            – jamieDoe
            Nov 27 '18 at 18:30













          • Yes, true. Try the edited answer with the revised full query.

            – Gustav
            Nov 28 '18 at 7:11











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490085%2fms-access-find-the-most-common-user-per-month-query%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0














          Try filtering on the month:



          Select 
          Format([Date1], "yyyymm") As YearMonth,
          (Select Top 1 T.ID
          From Table1 As T
          Where Format(T.[Date1], "yyyymm") = Format(Table1.[Date1], "yyyymm")
          Group By ID
          Order By Count(T.ID) Desc) As TopID
          From
          Table1
          Group By
          Format([Date1], "yyyymm")





          share|improve this answer


























          • Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

            – jamieDoe
            Nov 27 '18 at 18:30













          • Yes, true. Try the edited answer with the revised full query.

            – Gustav
            Nov 28 '18 at 7:11
















          0














          Try filtering on the month:



          Select 
          Format([Date1], "yyyymm") As YearMonth,
          (Select Top 1 T.ID
          From Table1 As T
          Where Format(T.[Date1], "yyyymm") = Format(Table1.[Date1], "yyyymm")
          Group By ID
          Order By Count(T.ID) Desc) As TopID
          From
          Table1
          Group By
          Format([Date1], "yyyymm")





          share|improve this answer


























          • Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

            – jamieDoe
            Nov 27 '18 at 18:30













          • Yes, true. Try the edited answer with the revised full query.

            – Gustav
            Nov 28 '18 at 7:11














          0












          0








          0







          Try filtering on the month:



          Select 
          Format([Date1], "yyyymm") As YearMonth,
          (Select Top 1 T.ID
          From Table1 As T
          Where Format(T.[Date1], "yyyymm") = Format(Table1.[Date1], "yyyymm")
          Group By ID
          Order By Count(T.ID) Desc) As TopID
          From
          Table1
          Group By
          Format([Date1], "yyyymm")





          share|improve this answer















          Try filtering on the month:



          Select 
          Format([Date1], "yyyymm") As YearMonth,
          (Select Top 1 T.ID
          From Table1 As T
          Where Format(T.[Date1], "yyyymm") = Format(Table1.[Date1], "yyyymm")
          Group By ID
          Order By Count(T.ID) Desc) As TopID
          From
          Table1
          Group By
          Format([Date1], "yyyymm")






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 28 '18 at 7:12

























          answered Nov 27 '18 at 11:02









          GustavGustav

          30k51936




          30k51936













          • Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

            – jamieDoe
            Nov 27 '18 at 18:30













          • Yes, true. Try the edited answer with the revised full query.

            – Gustav
            Nov 28 '18 at 7:11



















          • Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

            – jamieDoe
            Nov 27 '18 at 18:30













          • Yes, true. Try the edited answer with the revised full query.

            – Gustav
            Nov 28 '18 at 7:11

















          Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

          – jamieDoe
          Nov 27 '18 at 18:30







          Thanks but I get the message "Your query does not include the specified expression 'T![ID]' as part of an aggregate function".

          – jamieDoe
          Nov 27 '18 at 18:30















          Yes, true. Try the edited answer with the revised full query.

          – Gustav
          Nov 28 '18 at 7:11





          Yes, true. Try the edited answer with the revised full query.

          – Gustav
          Nov 28 '18 at 7:11




















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490085%2fms-access-find-the-most-common-user-per-month-query%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

          Calculate evaluation metrics using cross_val_predict sklearn

          Insert data from modal to MySQL (multiple modal on website)