Results 1 to 5 of 5

Thread: Counting days in a row

  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Counting days in a row

    Hi

    I've made a table with the fields :

    Day (date)
    Mood (text)


    I could fill the table with :

    Date1 : Happy
    Date2 : Happy
    Date3 : Tired
    Date4 : Sad
    Date5 : Sad
    Date6 : Happy


    Then I want to count how many days in a row a certain mood is present.

    The above data should be like this :
    happy 2, tired 1, sad 2, happy 1


    But I could use some help to how it's done ?!

  2. #2
    Join Date
    Mar 2011
    Posts
    1

    Counting Days in a row query reply

    hi it is easy to be done,
    I have created it and upload it here as MyMood.zip, download the file and study the two tables My Mood and Status and the Query1 which returns the result to you, it took me only two minutes to do it, not that complicated, hope it helps.
    Regards.
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    mood count

    This should do the trick:

    SELECT mood, count(mood)
    FROM tablename
    GROUP BY mood;

    Basically this will list each different value in the mood field and then count the number of occurrences of that value.

    You can also use the Query Designer in Access if you are not comfortable with SQL.

    Start a new query in the Design View, add the relevant table and add the mood field to the field list twice. click the Σ sign at the very top on the main Access toolbar to enable grouping. On the designer an extra row will be added for each field so you will have the field name, the table name, the word Total with Group By in each column. In one of the fields click on the Group By and a drop down selector will appear with a list of options. Scroll down until you find Count and click it. Run the query and you should get your count of moods.

    Hope this helps.

  4. #4
    Join Date
    Feb 2011
    Posts
    3
    Thx for your reply ...


    Unless I'm wrong, that will give me the totals for each mood, but i'm seeking sub-totals for consecutive moods.

  5. #5
    Join Date
    Mar 2011
    Posts
    2
    Ah - well spotted. Apologies for the confusion.

    Not sure if it can be done in one query. My approach would be to use VBA, create the recordset, read all the records to an array, create a counter variable and then loop through the array.

    if the mood value in Arr(1) = Arr(0) then counter = counter + 1

    if the mood is different then write the mood and the counter values to a string variable and reset the counter.

    Once you have completed the loop display the string in a text box or message box.

    Again not sure if it can be done in a single query - others may be able to correct me on that though.

    Hope this better than original solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •