Multiple Counts By Month?

[Roger Tregelles]

Hi Folks,

I need to create a query that I can use to create a chart from within
Access. I create monthly reports that show a 12 month snapshot of our
records (bar charts by month). The main information I would be getting from
a table would be Date Issued and Disposition type. I typically run a query
that uses the Criteria "Between xx/xx And xx/xx" with a specific disposition
"Like Rejected" to get 12 months of data and then manually input this into
Excel. I need a way to get 12 sets if numbers (counts) and then chart on
these from within Access. What would be the best way to do this? Would I
need to create 12 queries (one for each month) and then reference all 12 in
one query to chart on the actual counts? Sorry if this sounds really simple,
but I'm banging my head against the wall as I've never created anything like
this in Access before. Any help, direction or advice on creating queries
like these would be greatly appreciated. Thanks in advance.

Roger Tregelles

 

[Al Campagna]

Roger,
In your query, use the Month(YourDateField) to generate a month number from 1-12. Then
use that to sum by month and graph by month.
Month(YourDateField) MonthName SumOfSales
1 JAN 1,234
2 FEB 1,413 etc....

 

[Klatuu]

One trick you can consider if you need to get a colum of data from your table
into a column for each month in your query is to create 12 calculated fields.
Jan: IIf(DataIsForJan,[data],0) Feb: IIf(DataIsForFeb,[data],0) etc.

 

[Roger Tregelles]

Al,

Thanks for the reply. I'm not sure I completely understand how to create the
query based on your reply. How would I get the query to return a month
number exactly? Also, I'm not looking for a sum of records, but an actual
count. How would I get a count of records for a particular month? If you
could provide me with a few more details on how to construct the query I
would be forever grateful. The fields I need to get data from would be
"DateIssued" and "Disposition" only. Thanks again for any and all help you
can provide.

Roger Tregelles

 

[KARL DEWEY]

Create an update query and use this as the Update To --
Replace([YourFieldName],"w/vocals","")
or ir you want a space --
Replace([YourFieldName],"w/vocals"," ")

 

[Al Campagna]

In your existing chart query design mode use an empty column with
a Field of...
MonthNo : Month(YourDateField)
For every date, MonthNo will contain a value from 1 to 12. If you then GroupBy
MonthNo, and Sum on your "value" field, you'll get the sum by month.

Note: Please don't delete previous posts from a thread. It makes it easier to see the
flow of the problem, and see all the steps taken in order.

 

[Ken Sheridan]

Roger:

To count conditionally you sum the values of an expression which returns 1
or 0, using the IIF function e.g. to count the number of rows per month in
2006 where the Disposition value is "Rejected" you'd use something like this:

SELECT
MONTH(DateIssued) AS MonthIssued,
SUM(IIF(Dispostion= "Rejected",1,0)) AS RejectionCount
FROM YourTable
WHERE YEAR(DateIssued) = 2006
GROUP BY MONTH(DateIssued);

Ken Sheridan
Stafford, England