newbie question - counting data

[red6000]

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

 

[Douglas J Steele]

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.

 

[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.

 

[John Vinson]

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]

 

[red6000]

Wow, thanks.

works a treat.

Thanks for such a quick reply.

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

(no e-mails, please!)

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