Listing Number of Items Due Per Month

[Nerida via AccessMonster.com]

Hi there,

I have a database that tracks the CreateDate for different types of documents
documents (DocType). These documents need to be reviewed every two years. I
would like to create a report that show the number of documents that are due
for review each month by document type. I just don't know where to begin...
I've had a go at grouping in a regualr query and using a Cross Tab query but
I just can't seem to crack it. If any one can point me in the right
direction I'd be very grateful.

Kind regars,
Nerida

 

[BruceS]

Nerida,

What you need is a crosstab query and the easiest way to build one is to use
the Wizard. The trick is to remember what you want in "rows", "columns" and
for "value". In your case, you want the "row" to be document type, the
"columns" to be the month of the review date, and the "count" of the records
to be the value.

In the following example, I'm using "docID" as the autonumber index for the
"documents" table. "docType" is the document type field, and "docDate" is
the review date.

In the database window, click on Queries then "New" at the top.
Click "Crosstab query wizard" in the list that appears, then click OK.
Click on your documents table, then click Next.
For "Which fields do you want as the row headings?" select "docType" and
click ">" to move it over, then click Next.
For "Which fields values do you want as the column headings?", select
"docDate", then click Next.
For the "group" question, click "Month", then click Next.
For the "number" question, highlight "docID" and "Count", then click Next.
Give the query a name and click finish.

HTH,
Bruce