Report by Month

[Douglasrac]

Hi,

I want to create a Report that seems simple, but I can´t figure out how to make it.

I already have a Query with the following:

Number of Clients
Month
Production
Commission

So the query bring data per month and sum the production, number of clients and commission of that month.

I want a report that will give me:

Number of Clients
Production
Commission
% of growth of number of clients compared to last month
% of growth of number of clients compared to same month last year

The problem is two:

1) How can I make the report bring only data from the current month? Since the query have data from all months?
I thought about making a query that will bring data only for the current month, but then how can I calculate % compared to other months if it´s not there?

2) How to make those calculations of growth?

I´m almost getting the data from the query, pasting in excel and creating my own report, but seems dumb, since Access is certainly capable of doing that.

[jzwp11]

You will have to include the year in your initial query in order to separate out the monthly data of this year from previous years.

You will also have to filter the report based on the month and year you want. I would suggest using a form to input the month/year you want and use that to filter the report.

In terms of the growth fields in the query you will need either 2 nested queries or you can use the domain aggregate functions available within Access.

[Douglasrac]

I don't know how to do any of these.

Can't a report be filtered like a query?
I have several queries to filter by month, all I have to do is to enter in Design and change in a field the month I want. Easy and fast.
I don't want to create a form for that. Can't the report just detect the current month and only show data from this month? Also possible with a query.

How can I make the query show records in a range? I know, but I don't know the command.
I have data like 201007, 201008, 201009 which mean July 2010, August 2010, September 2010 and so on.
What I a criteria that will show only the last 12 months. What command it is?

And there is no way to make a calculation in the report? Just want to add in the corner, Field1 + Field2, somehting like that. Or the report just show, data nothing more?

[jzwp11]

In your original post you only mentioned that you had the month, I did not realize that you had the year and month already together in the field.

I have several queries to filter by month, all I have to do is to enter in Design and change in a field the month I want. Easy and fast.
I don't want to create a form for that. Can't the report just detect the current month and only show data from this month? Also possible with a query.

You can build the report based on a query and that query can be a parameter query for which you would provide the month or in your case the yyyymm. You would not have to change the design of the query

SELECT ...
FROM...
WHERE yourmonthfield=[Enter Desired Month]

When you run the above query, it will automatically prompt you for the desired month and you would enter whatever you want. There would be no need to change the design or have multiple queries.

How can I make the query show records in a range? I know, but I don't know the command.
I have data like 201007, 201008, 201009 which mean July 2010, August 2010, September 2010 and so on.
What I a criteria that will show only the last 12 months. What command it is?

There is no command, you just have to specify the beginning and ending periods in the criteria of the query

SELECT ...
FROM....
WHERE yourmonthfield BETWEEN 201001 AND 201012

Again, you can set this up as a parameter query by

WHERE yourmonthfield BETWEEN [Enter period start] and [Enter period end]

And there is no way to make a calculation in the report? Just want to add in the corner, Field1 + Field2, somehting like that. Or the report just show, data nothing more?

Yes you can use an expression in a report, but if the value you are after is not in the same record, you have to go out and get it using a different method (or you can just bring it in using the query on which the report is built as I suggested earlier). In either case, you can use the domain aggregate function DLookup() to get the previous month's value in order to calculate the % growth

For example, let's say that you are looking at October 2011 data for a client, neither the previous month's data (September 2011) nor the same month/previous year (October 2010) data are in the same record as the October 2011 data, so you will have to go lookup that data and bring it to a control in the report. The DLookup() function will do that.

If you use the query approach as I suggested, you could use either the Dlookup function or a nested query.

You will need to specify 2 criteria for the DLookup(), the client and the previous month which is just simply 1 less than the month shown for the current record (201111-201110=1) or in the case of the same month/previous year the difference would be 100 (201111-201011=100).

However, you will encounter a problem in January because the previous month is not obtained by subtracting 1
201101-201012=89

I am thinking that you may have to break apart the month from the year. To do this would require some conversion functions. My guess is that you may have to convert the value to a string (assuming that it is numeric now---don't know--you'll have to tell us that) using the cstr() function, then take the 2 right most characters using the right() function, then convert it back to an integer int() to strip off the leading zero. That could get a little complicated, so I'll wait for you to tell us whether the month field is text or numeric.

[Douglasrac]

Thanks for the help, I will use excel.

[jzwp11]

Sorry to hear that, but I understand. Access does have a longer learning curve than Excel, but I believe that it would be a much more powerful tool for you in the long run based on what I have learned about your project. The issues I have identified can be solved, it will just take a little time to get there. I can do it for you but the purpose of the forum is to help you learn how to do it.

If you decide to revisit your decision at some point in the future, we will be here to help.

[Douglasrac]

Yes I know. Access have a long learning curve. Like miles and miles.

I realize it's powerful and can do almost anything but the time and trouble to get there is just not worth it. Now I just exported the data to excel and I'm able to do so much. Not only what I wanted but several other ideas I got.

I can easily:

- Put data from several queries in one report page.
- I can make excel look only to the exact number I want (VLOOKUP), from the month I want, using a query that have all data, from all months and all years. No need to filter.
- Excel will make use of COUNTIF and count records and categorize them, using partial match. All from the same query.

As far as I worked with Reports in Access, you have one Report for one query. You can't gather data from a lot of queries, or play with the data the way you want, and make calculations and categorize etc.

Anyway, I'm satisfied with what I learned in Access until now, since I'm a complete noob. My database is capable of doings things that most programs you buy for the same purpose cannot do. The only thing that really bother me is the fact that it doesn't exist a global search. That is something really necessary these days. A way to search in the entire database, doesn't matter which table the data are. The fact that you need to go here and there to search is really old.

[jzwp11]

The only thing that really bother me is the fact that it doesn't exist a global search. That is something really necessary these days. A way to search in the entire database, doesn't matter which table the data are. The fact that you need to go here and there to search is really old.

If you have a database that is properly structured, you can bring data together and search it. It just takes a skilled programmer to make it seemless to the users. I have set up some databases with search forms where the user can specify any number of criteria. It is probably not quite like the global search you mention, but then again my application was geared toward specific users and data.

A lot of the on-line databases can be programmed to do the global searches, but still at the core is a relational database.