Access Report by Month

[kowen091010]

Hello! I am trying to do a report for work that has the following information. I have a table listing Client Name, Account Number, Officer Name, # of Accounts, Date Account Opened, Group #. I need the report to be grouped by client name and it needs to list the Account Number and officer name. It needs to show the information by month for each client. How do I get the report to list the customers information by month without splitting the information into multiple tables?


What I need for the report:

Client Name: John Doe Account #: 123456
January February March April
# Accounts 3 2 1 5
Client ID A A B C

Table I have:

Client Name: # Accounts Client ID Month
John Doe 5 A 12/01/10
John Doe 3 A 11/01/10
John Doe 2 B 10/01/10
Jane Doe 4 D 12/01/10
Jane Doe 2 E 11/01/10
Jane Doe 1 A 10/01/10

[June7]

In a query the report is based on (or the report's RecordSource sql statement), create a field with expression to extract month from date value, like:
MonthOpen: Month([Date Account Opened])

Now you have a field that can be used to group records by month. Are there multiple years in the database? Will need to also group by year or filter to only year of interest. Can create another field for year:
YearOpen: Year([Date Account Opened])

[kowen091010]

In a query the report is based on (or the report's RecordSource sql statement), create a field with expression to extract month from date value, like:
MonthOpen: Month([Date Account Opened])

Now you have a field that can be used to group records by month. Are there multiple years in the database? Will need to also group by year or filter to only year of interest. Can create another field for year:
YearOpen: Year([Date Account Opened])

I am able to group the data by month,but cannot get it to read as a vertical column header. Any suggestions?

[June7]

I don't understand. Can't get it to read as a vertical column header where? If you can group on this constructed field then it must be in the query.

[kowen091010]

I attached an example of how I would like the report to read. It is currently in a list format.

[June7]

Looks like you want to 'pivot' the data. A crosstab query of the grouping query might accomplish. If not, there are other techniques - more complicated queries or VBA code.

[kowen091010]

I will test it out! I cannot say I have used pivots before in Access but there is no time like the present to learn. Crosstab didn't seem to work for me. Chances are I am doing something wrong there too. Thank you so much for your help!

[June7]

I looked at your sample again. Will dates always be the first of the month? A crosstab can pivot the table to show date headings with total accounts for each ClientID/Name pair but can't get the ID under each date column. If you must have that will need more tricks to accomplish.

[kowen091010]

I will probably convert those dates from 12/01/2011 to December 2011. There will be information for every month going forward. I do need to have the ID under each month as well. I have been trying to figure this out for over a week now. I will try anything!

[June7]

Your data description above appears inconsistent with the image. There is no field for Officer name and Account Number. Your image shows grouping by Client, Account, Officer. Need more representative data in order to advise. Also the image shows only one Group# for each Client, Account, Officer, Month combination. Can you provide project (copy without confidential data) for analysis?

[kowen091010]

Here is an example database I built. I was able to get the information to work now the problem I am running into is getting the report to read by month & year. We have some months from 2010 & some from 2011 & I do not want to group them together. Any suggestions?

[June7]

You could filter the two base crosstab queries. Create a field in each:

YearOpen: Year(DateAccountOpen)

Put the desired year in the Criteria row.

You could use a parameter for the criteria. The input can be a prompt or reference to a textbox on form where the year is entered.