Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

newbie question - counting data

Discussion in 'Information Technology' started by red6000, Jul 28, 2006.

  1. red6000

    red6000
    Expand Collapse
    Guest

    Hi,

    I have a table with various columns of data. One of the Columns (the first
    column) contains a persons name, the second column contains a date (in
    format dd/mm/yyyy)

    I would like to be able to run a report/query that shows me the number of
    times each name appears for a chosen month (preferably in ascending order)

    How can this be achieved without having to export the data to excel?

    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    Newbie here, just wanted to introduce myself Interfaith Dialogues Jan 18, 2005
    Question About GGS Questions and Answers Aug 28, 2016
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Sikhi Questioning Faith Sikh Sikhi Sikhism Mar 3, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Create a new query.

    When prompted, select your table from the list that's presented.

    Drag the first two columns (the ones you mentioned below) from the table
    into the grid. Drag one additional column as well.

    Where the 2nd column appears in the grid, change ColumnName to WhatMonth:
    Month([ColumnName]). (Replace ColumnName with the real name of the column).
    Uncheck the "Show" check box below the field you just modified. In the
    Criteria cell below that, type [What Month?]

    Change the query into a Totals query. You do this either by clicking on the
    Sigma icon on the button bar, or selecting Totals from the View menu. You
    should notice a new row appearing in the grid labelled "Total:", preset to
    Group By under all 3 columns.

    Change "Group By" to "Where" in the Total: row under the column you modified
    above.

    Change "Group By" to "Count" in the Total: row under the 3rd column I told
    you to add above.

    Run your query.

    You should get an input box popping up labelled What Month?. Type the number
    of the month into that box (1 through 12).

    Assuming that's what you're looking for, save the query so that you can run
    it whenever you want.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "red6000" <red1000002001@yahoo.com> wrote in message
    news:44b54f41$0$22107$ed2619ec@ptn-nntp-reader01.plus.net...
    > Hi,
    >
    > I have a table with various columns of data. One of the Columns (the

    first
    > column) contains a persons name, the second column contains a date (in
    > format dd/mm/yyyy)
    >
    > I would like to be able to run a report/query that shows me the number of
    > times each name appears for a chosen month (preferably in ascending order)
    >
    > How can this be achieved without having to export the data to excel?
    >
    > Thanks
    >
    >
     
  4. schasteen

    schasteen
    Expand Collapse
    Guest

    Open a queary in design view. Click the Sigma icon on the toolbar. You will
    need 3 fields, the primary key field, name, and month([NameOfYourDateField]).
    In the total row under these, set to count for the primary key and Group by
    for the others. You can also sort the data as you wish.

    "red6000" wrote:

    > Hi,
    >
    > I have a table with various columns of data. One of the Columns (the first
    > column) contains a persons name, the second column contains a date (in
    > format dd/mm/yyyy)
    >
    > I would like to be able to run a report/query that shows me the number of
    > times each name appears for a chosen month (preferably in ascending order)
    >
    > How can this be achieved without having to export the data to excel?
    >
    > Thanks
    >
    >
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 12 Jul 2006 20:36:36 +0100, "red6000"
    <red1000002001@yahoo.com> wrote:

    >Hi,
    >
    >I have a table with various columns of data. One of the Columns (the first
    >column) contains a persons name, the second column contains a date (in
    >format dd/mm/yyyy)
    >
    >I would like to be able to run a report/query that shows me the number of
    >times each name appears for a chosen month (preferably in ascending order)
    >
    >How can this be achieved without having to export the data to excel?
    >
    >Thanks
    >


    A Totals query will do it for you.

    Create a new Query based on the table. Include the namefield TWICE and
    the datefield.

    Change it to a Totals query by clicking the Greek Sigma icon on the
    toolbar (it looks like a sideways W).

    Change the default Group By to "Count" under the second instance of
    the name field, and change it to "Where" under the datefield.

    On the Criteria line put

    >= DateSerial([Enter year:], [Enter month number, e.g. 5 for May:], 1) AND < DateSerial([Enter year:], [Enter month number, e.g. 5 for May:] + 1, 1)


    under the date field.

    Save this query, and (if you wish) base a Report on it. When you open
    the query (or the report) you'll be prompted for a year and a month,
    and you'll see the list of names that mongh and the count of
    occurances.

    John W. Vinson[MVP]
     
  6. red6000

    red6000
    Expand Collapse
    Guest

    Wow, thanks.

    works a treat.

    Thanks for such a quick reply.


    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23WIyr3epGHA.2400@TK2MSFTNGP03.phx.gbl...
    > Create a new query.
    >
    > When prompted, select your table from the list that's presented.
    >
    > Drag the first two columns (the ones you mentioned below) from the table
    > into the grid. Drag one additional column as well.
    >
    > Where the 2nd column appears in the grid, change ColumnName to WhatMonth:
    > Month([ColumnName]). (Replace ColumnName with the real name of the
    > column).
    > Uncheck the "Show" check box below the field you just modified. In the
    > Criteria cell below that, type [What Month?]
    >
    > Change the query into a Totals query. You do this either by clicking on
    > the
    > Sigma icon on the button bar, or selecting Totals from the View menu. You
    > should notice a new row appearing in the grid labelled "Total:", preset to
    > Group By under all 3 columns.
    >
    > Change "Group By" to "Where" in the Total: row under the column you
    > modified
    > above.
    >
    > Change "Group By" to "Count" in the Total: row under the 3rd column I told
    > you to add above.
    >
    > Run your query.
    >
    > You should get an input box popping up labelled What Month?. Type the
    > number
    > of the month into that box (1 through 12).
    >
    > Assuming that's what you're looking for, save the query so that you can
    > run
    > it whenever you want.
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "red6000" <red1000002001@yahoo.com> wrote in message
    > news:44b54f41$0$22107$ed2619ec@ptn-nntp-reader01.plus.net...
    >> Hi,
    >>
    >> I have a table with various columns of data. One of the Columns (the

    > first
    >> column) contains a persons name, the second column contains a date (in
    >> format dd/mm/yyyy)
    >>
    >> I would like to be able to run a report/query that shows me the number of
    >> times each name appears for a chosen month (preferably in ascending
    >> order)
    >>
    >> How can this be achieved without having to export the data to excel?
    >>
    >> Thanks
    >>
    >>

    >
    >
     

Share This Page