Count of records split by months

[Guest]

Hi there,

I'm trying to count all the audits planned within the last year, grouped by
month.

What I'm looking for is a function that will firstly group the planned dates
by month, then count how many records are within that month, then export the
months and the count to an Excel database.

Can anyone help? I'm going round in circles!

Thanks

 

[Guest]

Hi Tigger,

Hm, going round in circles, named Tigger, reminiscent of A.A.Milne.

Assuming your date is a Date field and you want to accumulate by calendar
months use the DatePart function to extract the month number. The expression
for your Query Grid will look something like

DatePart([MyDate],"m")

But that's not quite right since you need to qualify it by the year.

(DatePart([MyDate],"yyyy") * 100) + DatePart([MyDate],"m")

will give you an integer in yyyymm format.

Use the value returned for 'Group By.' Then in another column on your query
grid choose a field where there will always be a value, the primary key is a
good choice, and do a 'Count' on that field.

Play with this until you're happy. Have a look at the SQL it generates.

Exporting the results to Excel is a different game of 'Pooh Sticks.' Get
back to me when you have the query working to your satisfaction.

Rod

 

[Guest]

Hi Rod,

Yes, I live in the 100 Acre Wood

Just to clarify, I need to create a new query grid, add my primary key and
the field that I wish to qualify (in this case, Audit ID and Initial start
date) and then insert the expression into a new column.

When I do this, I get an Invalid procedure call error message.

How do I best put this in the query (sorry for being a bit dim)

Thanks

Hi Tigger,

Hm, going round in circles, named Tigger, reminiscent of A.A.Milne.

Assuming your date is a Date field and you want to accumulate by calendar
months use the DatePart function to extract the month number. The expression
for your Query Grid will look something like

DatePart([MyDate],"m")

But that's not quite right since you need to qualify it by the year.

(DatePart([MyDate],"yyyy") * 100) + DatePart([MyDate],"m")

will give you an integer in yyyymm format.

Use the value returned for 'Group By.' Then in another column on your query
grid choose a field where there will always be a value, the primary key is a
good choice, and do a 'Count' on that field.

Play with this until you're happy. Have a look at the SQL it generates.

Exporting the results to Excel is a different game of 'Pooh Sticks.' Get
back to me when you have the query working to your satisfaction.

Rod

Hi there,

I'm trying to count all the audits planned within the last year, grouped by
month.

What I'm looking for is a function that will firstly group the planned dates
by month, then count how many records are within that month, then export the
months and the count to an Excel database.

Can anyone help? I'm going round in circles!

Thanks

 

[raskew via AccessMonster.com]

In the example, the interval and date are reversed in the DatePart calls.
Try:

? DatePart("yyyy",Date()) * 100 + DatePart("m", Date())
200711

Bob

Hi Rod,

Yes, I live in the 100 Acre Wood

Just to clarify, I need to create a new query grid, add my primary key and
the field that I wish to qualify (in this case, Audit ID and Initial start
date) and then insert the expression into a new column.

When I do this, I get an Invalid procedure call error message.

How do I best put this in the query (sorry for being a bit dim)

Thanks

Hi Tigger,

[quoted text clipped - 35 lines]

 

[Rod Plastow]

Bob,

Dyslexia rules KO. You are of course absolutely right and apologies to
Tigger for any wasted time. The only mitigating facts in my defence are that
it was late at night and one San Miguel too many.

Tigger,

Yes, create a new query and this will display an empty grid. Select your
table from the choices offered.

Drag and drop AuditId into the first column into the Field: row. Place the
cursor in the Field: row of the next column and press Shift + F2. (This is
not necessary but does open the edit window where everything can be seen.

In this window type:

AuditMonth: DatePart("yyyy",[MyDate]) * 100 + DatePart("m", [MyDate])

substituting your date field name for the word MyDate above. Close the edit
window.

Now click on the sigma button that is somewhere on your toolbar depending
upon which version of Access you are using. A new row opens in the grid
named Totals:

Using this row select Count in the first (AuditId) column and Group By in
the other column.

Test the query.

Give my regards to Owl.

Rod

"raskew via AccessMonster.com"
wrote:http://www.microsoft.com/wn3/aspx/p...ice/community/en-us/settings.xml&sdgloc=en-US

In the example, the interval and date are reversed in the DatePart calls.
Try:

? DatePart("yyyy",Date()) * 100 + DatePart("m", Date())
200711

Bob

Hi Rod,

Yes, I live in the 100 Acre Wood

Just to clarify, I need to create a new query grid, add my primary key and
the field that I wish to qualify (in this case, Audit ID and Initial start
date) and then insert the expression into a new column.

When I do this, I get an Invalid procedure call error message.

How do I best put this in the query (sorry for being a bit dim)

Thanks

Hi Tigger,

[quoted text clipped - 35 lines]

Thanks

 

[tigger]

Beautiful! Works like a dream.

Owl says hi

Bob,

Dyslexia rules KO. You are of course absolutely right and apologies to
Tigger for any wasted time. The only mitigating facts in my defence are that
it was late at night and one San Miguel too many.

Tigger,

Yes, create a new query and this will display an empty grid. Select your
table from the choices offered.

Drag and drop AuditId into the first column into the Field: row. Place the
cursor in the Field: row of the next column and press Shift + F2. (This is
not necessary but does open the edit window where everything can be seen.

In this window type:

AuditMonth: DatePart("yyyy",[MyDate]) * 100 + DatePart("m", [MyDate])

substituting your date field name for the word MyDate above. Close the edit
window.

Now click on the sigma button that is somewhere on your toolbar depending
upon which version of Access you are using. A new row opens in the grid
named Totals:

Using this row select Count in the first (AuditId) column and Group By in
the other column.

Test the query.

Give my regards to Owl.

Rod

"raskew via AccessMonster.com"
wrote:http://www.microsoft.com/wn3/aspx/p...ice/community/en-us/settings.xml&sdgloc=en-US

In the example, the interval and date are reversed in the DatePart calls.
Try:

? DatePart("yyyy",Date()) * 100 + DatePart("m", Date())
200711

Bob

Hi Rod,

Yes, I live in the 100 Acre Wood

Just to clarify, I need to create a new query grid, add my primary key and
the field that I wish to qualify (in this case, Audit ID and Initial start
date) and then insert the expression into a new column.

When I do this, I get an Invalid procedure call error message.

How do I best put this in the query (sorry for being a bit dim)

Thanks

Hi Tigger,

[quoted text clipped - 35 lines]

Thanks

 

[tigger]

Got another one for you ...

I'd like to compare one query with another, so that I can export the data
into Excel.

I have my query to identify planned audits split by month (as below) and one
to identify audits that occurred split by month. What I'd like to do is
compare the number of audits planned against the number that occurred, split
by month.

Can you help with this?

Thanks

Bob,

Dyslexia rules KO. You are of course absolutely right and apologies to
Tigger for any wasted time. The only mitigating facts in my defence are that
it was late at night and one San Miguel too many.

Tigger,

Yes, create a new query and this will display an empty grid. Select your
table from the choices offered.

Drag and drop AuditId into the first column into the Field: row. Place the
cursor in the Field: row of the next column and press Shift + F2. (This is
not necessary but does open the edit window where everything can be seen.

In this window type:

AuditMonth: DatePart("yyyy",[MyDate]) * 100 + DatePart("m", [MyDate])

substituting your date field name for the word MyDate above. Close the edit
window.

Now click on the sigma button that is somewhere on your toolbar depending
upon which version of Access you are using. A new row opens in the grid
named Totals:

Using this row select Count in the first (AuditId) column and Group By in
the other column.

Test the query.

Give my regards to Owl.

Rod

"raskew via AccessMonster.com"
wrote:http://www.microsoft.com/wn3/aspx/p...ice/community/en-us/settings.xml&sdgloc=en-US

In the example, the interval and date are reversed in the DatePart calls.
Try:

? DatePart("yyyy",Date()) * 100 + DatePart("m", Date())
200711

Bob

Hi Rod,

Yes, I live in the 100 Acre Wood

Just to clarify, I need to create a new query grid, add my primary key and
the field that I wish to qualify (in this case, Audit ID and Initial start
date) and then insert the expression into a new column.

When I do this, I get an Invalid procedure call error message.

How do I best put this in the query (sorry for being a bit dim)

Thanks

Hi Tigger,

[quoted text clipped - 35 lines]

Thanks

 

[Rod Plastow]

But of course!

You will begin with two queries, almost identical, one for counting planned
audits and one for counting actual audits. Let's assume they are called
qryPlannedCount and qryActualCount. Each query returns two columns that for
the sake of this post I name CountOfAuditId and AuditMonth.

Create a new empty query grid but instead of calling up tables call up the
two queries. Drag and drop the AuditMonth from one query to AuditMonth on
the othe query. A line appears indicating that a relationship has been
created. Right-click on this line and choose the best option from the
choices offered. I suggest you choose to display all records from
qryPlannedCount and those from qryActualCount that match.

Now drag one of the AuditMonth (qryPlannedCount) into the first column of
your grid. Likewise drag the two counts into successive columns.

Test the query as is.

You can add some more meaningful column names if you wish simply by typing
the name folowed by a colon in front of the field name. So using my field
names I would enter Month:[AuditMonth] as the name for the first column,
Planned Count:[CountOfAuditId] as the second name and Actual
Count:[CountOfAuditId] as the third name.

Test again.

Now you need to guard against the possibility that there were no actual
audits during a month. The query will return Null for the count so your
third name should better be Actual Count:Nz([CountOfAuditId],0) to force a
zero when there are no records.

Test again.

There is a flaw in this solution. Months where there were no planned audits
but where there were actual audits will not be incorporated in the results.
The only way around this is to generate a third table/query containing every
month known to the system. It is possible using a Union query but a trifle
messy. I believe you need to retrieve the union then eliminate duplicates as
a second operation; the DISTINCT key word operates on each part of the union
separately and not on the overall result.

I hear Christopher Robin is not well; someone told me he's gone down with
Alice.

Rod

 

[tigger]

Hi Rod,

I will need to display all actual audits regardless of whether there were
planned ones in the month, so I think I'm going to need some help with the
union query

Thanks for your help so far.

 

[Rod Plastow]

It's late here now - I'll get back to you tomorrow.

Rod

Hi Rod,

I will need to display all actual audits regardless of whether there were
planned ones in the month, so I think I'm going to need some help with the
union query

Thanks for your help so far.

But of course!

You will begin with two queries, almost identical, one for counting planned
audits and one for counting actual audits. Let's assume they are called
qryPlannedCount and qryActualCount. Each query returns two columns that for
the sake of this post I name CountOfAuditId and AuditMonth.

Create a new empty query grid but instead of calling up tables call up the
two queries. Drag and drop the AuditMonth from one query to AuditMonth on
the othe query. A line appears indicating that a relationship has been
created. Right-click on this line and choose the best option from the
choices offered. I suggest you choose to display all records from
qryPlannedCount and those from qryActualCount that match.

Now drag one of the AuditMonth (qryPlannedCount) into the first column of
your grid. Likewise drag the two counts into successive columns.

Test the query as is.

You can add some more meaningful column names if you wish simply by typing
the name folowed by a colon in front of the field name. So using my field
names I would enter Month:[AuditMonth] as the name for the first column,
Planned Count:[CountOfAuditId] as the second name and Actual
Count:[CountOfAuditId] as the third name.

Test again.

Now you need to guard against the possibility that there were no actual
audits during a month. The query will return Null for the count so your
third name should better be Actual Count:Nz([CountOfAuditId],0) to force a
zero when there are no records.

Test again.

There is a flaw in this solution. Months where there were no planned audits
but where there were actual audits will not be incorporated in the results.
The only way around this is to generate a third table/query containing every
month known to the system. It is possible using a Union query but a trifle
messy. I believe you need to retrieve the union then eliminate duplicates as
a second operation; the DISTINCT key word operates on each part of the union
separately and not on the overall result.

I hear Christopher Robin is not well; someone told me he's gone down with
Alice.

Rod

 

[Anu]

Create a Pivot TAble with the Dates in the Row Area. USe the group by months
option in pivot to group the data
Anu

It's late here now - I'll get back to you tomorrow.

Rod

Hi Rod,

I will need to display all actual audits regardless of whether there were
planned ones in the month, so I think I'm going to need some help with the
union query

Thanks for your help so far.

But of course!

You will begin with two queries, almost identical, one for counting planned
audits and one for counting actual audits. Let's assume they are called
qryPlannedCount and qryActualCount. Each query returns two columns that for
the sake of this post I name CountOfAuditId and AuditMonth.

Create a new empty query grid but instead of calling up tables call up the
two queries. Drag and drop the AuditMonth from one query to AuditMonth on
the othe query. A line appears indicating that a relationship has been
created. Right-click on this line and choose the best option from the
choices offered. I suggest you choose to display all records from
qryPlannedCount and those from qryActualCount that match.

Now drag one of the AuditMonth (qryPlannedCount) into the first column of
your grid. Likewise drag the two counts into successive columns.

Test the query as is.

You can add some more meaningful column names if you wish simply by typing
the name folowed by a colon in front of the field name. So using my field
names I would enter Month:[AuditMonth] as the name for the first column,
Planned Count:[CountOfAuditId] as the second name and Actual
Count:[CountOfAuditId] as the third name.

Test again.

Now you need to guard against the possibility that there were no actual
audits during a month. The query will return Null for the count so your
third name should better be Actual Count:Nz([CountOfAuditId],0) to force a
zero when there are no records.

Test again.

There is a flaw in this solution. Months where there were no planned audits
but where there were actual audits will not be incorporated in the results.
The only way around this is to generate a third table/query containing every
month known to the system. It is possible using a Union query but a trifle
messy. I believe you need to retrieve the union then eliminate duplicates as
a second operation; the DISTINCT key word operates on each part of the union
separately and not on the overall result.

I hear Christopher Robin is not well; someone told me he's gone down with
Alice.

Rod

 

[Rod Plastow]

Hi Tigger,

I'm having difficulty imagining how a pivot table helps in this situation.

Anyway here's my solution. You need to specify two more queries. Here's
the first that I have named qryAuditMonthsX. Unfortunately you cannot use
the grid to specify this query so you need to type the SQL into the SQL
window.

SELECT DISTINCT
(DatePart("yyyy",[PlannedDate])*100)+DatePart("m",[PlannedDate]) AS [Audit
Month]
FROM Table1 UNION ALL SELECT DISTINCT
(DatePart("yyyy",[ActualDate])*100)+DatePart("m",[ActualDate]) AS [Audit
Month]
FROM Table2;

Sustitute your own field and table names.

The next query I call qryAuditMonths. You can use the grid but here's the
SQL.

SELECT DISTINCT qryAuditMonthsX.[Audit Month]
FROM qryAuditMonthsX
ORDER BY qryAuditMonthsX.[Audit Month];

The first query concatenates all the planned and actual audit months. The
second query which is the one you want simply purges the result set of
duplicates. The result set is a list of months where audit activity
occurred, planned, actual or both.

Now very similar to an earlier example call up qryAuditMonths and your two
count queries into the query design grid. This time however join the audit
month from the new query to each of the count queries in turn. Make sure the
join type specifies all records from qryAuditMonths and only those that are
equal from the count queries.

Construct your query in the grid. You need to use the Nz function on both
count fields to force a zero result if either the planned or the actual count
is missing.

Rod

 

[tigger]

Hi Rod,

Thanks for this - it works great!

I also found the crosstab query wizard, which has given me more information
than I need but essentially allows me to view the total of all audits planned
and then the status (i.e. completed, in progress, etc) which total up to the
audits conducted.

Thanks for your help!

 

[Rod Plastow]

Hi Tigger,

Glad to hear it. Yes the cross tab queries are very powerful analysis tools
but I couldn't imagine how you would handle two values (planned and actual)
in each cell and if the horizontal and vertical variable were the months then
you only have results on the diagonal (nothing actually wrong with that).

BTW before someone else points it out, if you drop the ALL command from my
first query above you don't need the second query as the UNION command by
itself does not concatenate duplicates. You would however need to add an
ORDER BY clause to the first query.

Rod