Using a query to create a count...

[Guest]

I have a table with dates in it. What I am trying to do is to create a query
that I can get the number of times for a 01/**/**** (January date) through
the year? I need to know how to set this up? What kind of iIF statement to
use if needed, or if I am making a mountain out of a mole hill?

This is what I want as a final table:

January Feb March ..... as a table
3 2 1 ..... as the number of times for that month.

 

[Jeff Boyce]

Thomas

Create a new query in design view.

Add the table that has this date-related field.

Add a new field in the query output grid, something like:
NewField: Month([YourDateField])

Also add the rowID field.

Now change the query to a Totals query by clicking on the "sigma" toolbar
button.

Keep the GroupBy for the NewField, and change the GroupBy to Count for the
rowID field.

NOTE -- very important -- this only gets you totals by month ... and if your
dates span more than one year, you'll be totally by month ACROSS YEARS.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

SELECT Month(SomeDate) as The MonthNumber, Count(SomeDate) as the Count
FROM SomeTable
GROUP BY Month(SomeDate)

Or use a crosstab query
TRANSFORM Count(SomeDate) as TheCount
SELECT "1", Format(SomeDate,"mmmm") as theMonth
FROM SomeTable
GROUP BY "1"
PIVOT Format(SomeDate,"mmmm") IN ("January","February",...,"December")

In the query grid
-- Select your table
-- Add the DateField twice and enter "1" in a field
-- Change one date field to Format(SomeDate,"mmmm")
-- Select Query: Crosstab from the menu
-- Change Group By to Count under the unmodified datefield
-- Under 1 select Row Heading in Crosstab Row
-- Under calculated MonthName select Column Heading
-- Under the count select Value
-- Right Click in the gray area and show properties for the query
-- in the Column Heading box type in the month names in order separated by a
comma (or semi-colon if that is your separator character)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Jeff,

I can use my "date field" in the query to group by, correct? That way for
January I have one row and one number?
--
Thomas

Thomas

Create a new query in design view.

Add the table that has this date-related field.

Add a new field in the query output grid, something like:
NewField: Month([YourDateField])

Also add the rowID field.

Now change the query to a Totals query by clicking on the "sigma" toolbar
button.

Keep the GroupBy for the NewField, and change the GroupBy to Count for the
rowID field.

NOTE -- very important -- this only gets you totals by month ... and if your
dates span more than one year, you'll be totally by month ACROSS YEARS.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with dates in it. What I am trying to do is to create a
query
that I can get the number of times for a 01/**/**** (January date) through
the year? I need to know how to set this up? What kind of iIF statement
to
use if needed, or if I am making a mountain out of a mole hill?

This is what I want as a final table:

January Feb March ..... as a table
3 2 1 ..... as the number of times for that month.

 

[John W. Vinson]

I have a table with dates in it. What I am trying to do is to create a query
that I can get the number of times for a 01/**/**** (January date) through
the year? I need to know how to set this up? What kind of iIF statement to
use if needed, or if I am making a mountain out of a mole hill?

This is what I want as a final table:

January Feb March ..... as a table
3 2 1 ..... as the number of times for that month.

You certainly do not want this AS A TABLE - it's grossly denormalized!

However, you can easily generate a datasheet AS A QUERY - use the Crosstab
Query wizard.

John W. Vinson [MVP]

 

[Jeff Boyce]

If you use your date field, and not the MONTH of the value in the date
field, you will be grouping by the value(s) in the date field, i.e.,
individual dates.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I can use my "date field" in the query to group by, correct? That way for
January I have one row and one number?
--
Thomas

Thomas

Create a new query in design view.

Add the table that has this date-related field.

Add a new field in the query output grid, something like:
NewField: Month([YourDateField])

Also add the rowID field.

Now change the query to a Totals query by clicking on the "sigma" toolbar
button.

Keep the GroupBy for the NewField, and change the GroupBy to Count for
the
rowID field.

NOTE -- very important -- this only gets you totals by month ... and if
your
dates span more than one year, you'll be totally by month ACROSS YEARS.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table with dates in it. What I am trying to do is to create a
query
that I can get the number of times for a 01/**/**** (January date)
through
the year? I need to know how to set this up? What kind of iIF
statement
to
use if needed, or if I am making a mountain out of a mole hill?

This is what I want as a final table:

January Feb March ..... as a table
3 2 1 ..... as the number of times for that month.