Counting Months

[Guest]

Hello,

I am trying to create a report that will give me the total number of quotes
submitted for the month of January, February, March, so on. My table lists
the date the quote was submitted and the value. Any help is appreciated.

Thanks
Mark

 

[John Vinson]

Hello,

I am trying to create a report that will give me the total number of quotes
submitted for the month of January, February, March, so on. My table lists
the date the quote was submitted and the value. Any help is appreciated.

Thanks
Mark

Add a calculated field to the Query:

YearMonth: Format([quote date], "yyyymm")

Make it a Totals query and group by this field.

John W. Vinson[MVP]

 

[Guest]

Thanks for the quick response.

I am new to Access, is there anyway you can go into more detail on how to
create the query and how I can get my report to access it. All I want to be
able to do is get the total number quote quotes submitted for the month of
January.

Thank you again.
Mark

Hello,

I am trying to create a report that will give me the total number of quotes
submitted for the month of January, February, March, so on. My table lists
the date the quote was submitted and the value. Any help is appreciated.

Thanks
Mark

Add a calculated field to the Query:

YearMonth: Format([quote date], "yyyymm")

Make it a Totals query and group by this field.

John W. Vinson[MVP]

 

[John Vinson]

Thanks for the quick response.

I am new to Access, is there anyway you can go into more detail on how to
create the query and how I can get my report to access it. All I want to be
able to do is get the total number quote quotes submitted for the month of
January.

That's not what you asked for above. You asked for January, February,
etc. Which do you want?

Assuming the broader (each month) option, create a new Query based on
your Table. Add JUST the primary key field of the table (for
counting). In a vacant Field cell type

SortDate: Format([yourdatefield], "yyyymm")

This text string will contain 200601, 200602, etc.

Click the Greek Sigma icon (looks like a sideways M). A new Total line
will appear. Change the default Group By under the primary key field
to Count.

If you want to filter the records to (say) the current year, add the
date field; change the totals row entry to Where; and put

= DateSerial(Year(Date()), 1, 1)

on the criteria line.

Save this query and use it as the Recordsource for your report -
create a new report using the wizard, and select the query as the
source of your data.

John W. Vinson[MVP]

 

[Guest]

Hi John,

Thanks for the info. It works great. I have one more question. Now that I
have created the query, how do I configure the text box in my report for
January to show only the quotes submitted in January? I have the new query
as the record source for the report. Your help is appreciated.

Thanks
Mark

Thanks for the quick response.

I am new to Access, is there anyway you can go into more detail on how to
create the query and how I can get my report to access it. All I want to be
able to do is get the total number quote quotes submitted for the month of
January.

That's not what you asked for above. You asked for January, February,
etc. Which do you want?

Assuming the broader (each month) option, create a new Query based on
your Table. Add JUST the primary key field of the table (for
counting). In a vacant Field cell type

SortDate: Format([yourdatefield], "yyyymm")

This text string will contain 200601, 200602, etc.

Click the Greek Sigma icon (looks like a sideways M). A new Total line
will appear. Change the default Group By under the primary key field
to Count.

If you want to filter the records to (say) the current year, add the
date field; change the totals row entry to Where; and put

= DateSerial(Year(Date()), 1, 1)

on the criteria line.

Save this query and use it as the Recordsource for your report -
create a new report using the wizard, and select the query as the
source of your data.

John W. Vinson[MVP]

 

[John Vinson]

Thanks for the info. It works great. I have one more question. Now that I
have created the query, how do I configure the text box in my report for
January to show only the quotes submitted in January? I have the new query
as the record source for the report. Your help is appreciated.

Add the date field to the query; select the "Where" operator on the
totals row; and put any criterion you like on the date, such as

DateSerial(Year(Date()), 1, 1) AND <= Date()

to get year to date, or

DateSerial(Year(Date()), 1, 1) AND < DateSerial(Year(Date()), 2, 1)

to get just January results whenever you run the report.

John W. Vinson[MVP]

 

[Guest]

I noticed if I put > DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 3,1) I get both January and February. I would like
to get just January or get just February. Will I be able to put individual
totals for January through December on the same report page? I hope I am not
being a pain. Your help is very much appreciated.

Thanks
Mark

 

[John Vinson]

I noticed if I put > DateSerial(Year(Date()), 1, 1) AND <
DateSerial(Year(Date()), 3,1) I get both January and February. I would like
to get just January or get just February. Will I be able to put individual
totals for January through December on the same report page? I hope I am not
being a pain. Your help is very much appreciated.

I guess I'm not understanding what you want.

If you want data for just one month, use a criterion that gives you
just one month.

If you want to see more than one month on the report, use a criterion
that gives you both months, and you'll see both months. The Totals
query as written (at least as I intended you to write it!) gives you
one row per month for all the dates you select in the query.

Your question seems to be self-contradictory: you want to see just one
month, and you want to see all months on the same page. What DO you
want?

John W. Vinson[MVP]

 

[Guest]

John,

I apologize for not making things clear. I will try one more time. I have
one report I do each month. On this report I have listed all twelve months
down the left side of the page and to the right of each month I have a column
that will list the total number of quotes submitted for that month. Please
see example:

Month Number
January 16
February 47
March 24
April 20
May 38
June 24
July 22
August 22
September 16
October
November
December

The query you gave me does give me one month per row, what I don't
understand is what to put in the text box on my report for each month that
will give me the total for that month. I hope this explains what I am
looking for. Thank you for all your help and patience.

Mark

 

[John Vinson]

The query you gave me does give me one month per row, what I don't
understand is what to put in the text box on my report for each month that
will give me the total for that month. I hope this explains what I am
looking for. Thank you for all your help and patience.

Again, I don't know the structure of your table, or how a "quote" is
defined - but if each row of your table is a quote, include any
non-null field from the table (the Primary Key is a good choice) in
the Totals query; on the Totals row select Count.

John W. Vinson[MVP]