Count field in report other than report header or footer 2

[rjmccafferty]

Is there any way at all, other than using subreports, to get counts into other sections of a report?

As an example, I first tried a field of =count(*) in the report header or footer and named the field CountAll .
Then, in another section of the report a created a field and for source used =CountAll. That did not work. Are there any other tricks that might work?

 

[jebry]

Hi!

Base your report on a query and include a count field in the query. Then you can put the count field anywhere and get a total count on any group you are working with.

hth
Jeff Bridgham

 

[rjmccafferty]

My difficulty with that is that I am doing counts in a lot of fields. When I try to do GroupBy queries (using the sigma sign on the tool bar) and

1. use the Count option for a number of different fields, or
2. count how many of several different entries are in the same field,

this system seems to fail me.

Do I need a better lesson in grouping at the query level? It is not an easy subject to read about. I have quite a number of books on Access and none go into any real detail in this area.

 

[jebry]

Hi!

Do your count on just one field. Do a group by on the fields you are interested in. I forgot to mention one important thing. Do your count query with just the fields you are interested in grouping by(which may be just one field). Then, when you do the full query to base your report on, you can add the count query as a table and join them on the group by field. Add the count field to your final query and now the count for the group will be attached to every detail record in the group! This will free up the count field to be placed anywhere in the report.

hth
Jeff Bridgham

 

[JoeMiller]

What is it you're trying to count, what are some examples of the data you're counting and what are the relevant table/query/field/etc... names? This will help us determine what the best method for you is.

Joe Miller
joe.miller@flotech.net

 

[rjmccafferty]

This particular report tallies certain data from marketing calls for management oversight. The query will look at all records within a given date range specified by the person printing the report.

1. ActivityType field: How many entries were CA (for call attempted but left message or scheduled call back, etc), how many were CM (for call made, in which we had a meaningful conversation). There are several other possible entries, but only these two need to be counted at this point.

2. MeetingDate field: How many records have an entry in this field (which indicates a meeting was arranged, as opposed to no entry in the field which indicates no meeting was arranged during this call).

What I have done thus far is use a lot of sub-reports. The first subreport has two fields in its report head and nothing in page header or detail sections. It is simply the use of two =dcount functions looking for CA and CM entries respectively to acommodate item 1 above.

Another sub-report lists all the meetings scheduled and in the report footer I use the basic =count(*) for the number.

**********************************************
But I do some work for another company in which I need to count as many as 3 or 4 specific entries per field and do this for as many as 4 or 5 fields. It has to do with tallying various actions for which he can charge differing amounts of money.

For example, if he generates web based leads, he charges $.50 each. If it is another type of lead he charges $.95 each.

We also need to count how many records show a monthly income (in a car loan application) that exceeds the minimum amount in another table (the minimum varies by dealer). My first step in this task is to calculate a field that leaves me with a Yes or No result from an iif statement. Then I want to count how many Yes entries there are. The ones with a Yes in this calculated field have another task generated for which he can charge yet another fee.

And, of course, the dealers do want to see all of this information in one report and would like all the calculations (count of specific records, which is then used to be multiplied by dollar amounts in yet another calculated field) to show up in the same place in the report, either the beginning or the end.

Is this any clearer than Mud? Have I gotten up to sewer water clarity yet?

 

[JoeMiller]

Much better! When presented with such a problem, I normally make total queries that give me the numbers I'm looking for and then use a DLookup to retrieve the total when needed. The trick is to make sure that the total queries are based on the query that supplies the detail to the report. The only thing you have to watch out for is when entering parameters (you mentioned start date, end date) that they are in a place (such as a form) that the subsequent calls to the query from the total queries and the DLookups and not just as parameters in the query itself.

Now, let's just look at your first one ActivityType (where you mentioned CA, CM, etc..). Make a new query based on the detail query with the dates (so our totals are correct) and place the ActivityType field in the grid and then the UniqueID for the records, make it a group by query, grouping on ActivityType and counting your UniqueID. This should give you our Activity Types and the number of (activities?) associated with that type in the dates specified. Now use a DLookup in the control source of a control on your report to get the value:

=DLookup("[CountOfUniqueID]","MyQueryName","[ActivityType]='CA'&quot

Then you'd get the Count of CA calltypes. Need a total of all calls? perform a DSum on the [CountOfUniqueID] or use the RunningSum feature of the controls in your report. As for the meeting dates, just follow the same general format in another query based on your detail query.

Some people will tell you that they don't like the Domain Aggregate functions because they are slow, don't take advantage of indexes, etc... but I've never had any major difficulties with them and it doesn't sound like you're dealing with hundreds of thousands of records. Hopefully that helps.

Joe Miller
joe.miller@flotech.net

 

[rjmccafferty]

I took a first look at doing this and it seems like it will work well. I assume I will use al three queries (1 for details, 2 for totals) as the basis for my report and easily relate them by common fields.

I am not sure, however, how to deal with the parameter aspect (date range) outside of the detail query. Obviously, when I use it in the detail query and then base the totals queries on the detail query, I get asked for the date range once for each query.

The way I launch other reports is to let the users:
1. press a Reports option on a menu bar followed by InteractiveReports as a submenu item.
2. This brings up a form that lets users select the report they want to run from a list (command buttons).
3. When the user presses the correct command button, the reports launch.
4. In most cases, the report is based on a parameter query that asks for dates, initials of the person the report is about, and/or information about the product involved.

It is easy for the users, is easy for me to change on the fly when necessary and allows for a minimum of code to be used. In this report, however, the parameter query is a large obstacle. Do you have suggestions of how to enter the date range within the above system without adding a lot of steps to the process?

One option that comes to my inexperienced mind is to have the command button bring up a form into which the date range is entered and let the query draw the dates from that form. I believe I could add the command to close this form to the command button after the command that opens the report.

Your suggestions? (And, in the words of TEF, bless your pea-pickin' little heart for taking so much time to help)

 

[rjmccafferty]

And as I play with this a bit, another question arises that I believe, but do not want to assume, will be answered in your response about how to ask for the date range upon which to base the report.

I definitely need to use a subreport to show some of the detail and again do not want to have to specify the date range as an additional parameter.

 

[JoeMiller]

I commonly place parameters I need on the form that will be calling the report. You mentioned the form where the user selects the report, why not have textboxes on that form that are then set in the criteria of the query? The controls are easy to reference in the queries themselves, replace the current style [Enter date:] parameter with code to look at the form:

[Forms]![MyFormName]![MyControlName]

A popup form would work as well, though I don't see the need to add another form when you can instead place the controls on the calling form.

As for the date range, I don't understand, I thought you needed to make them put in two dates (a start and an end date) for your reports to get the right answer? If that's not the case then I misunderstood you and forget what I was talking about with the date ranges. Otherwise, put to controls on your form to store the startdate and the enddate and then reference them in your between statement:

Between [Forms]![MyFormName]![MyControlName1] And [Forms]![MyFormName]![MyControlName2]

HTH Joe Miller
joe.miller@flotech.net

 

[rjmccafferty]

You mean you can't read my mind? Sorry. Let me slow down and try to be clearer.

I need to have information in a report that covers varying date ranges. This has to do with our Managers calling potential investors (large institutional investors). The report needs to have essentially the following (all for the date range specified by the user). I use two tables, one for information about the individual call being reported, a second that allows the user to list any number of follow up duties/things to be mailed. Kind of like invoice and lineItem tables in a one to many relationship, if that analogy helps at all.

*Number of calls attempted
*Number of calls completed (where they actually reach the proper person)
[Aside: One of the fields has entries for CA and CM for the above]

*Number of meetings arranged (count the records where this field is not blank)
*Who those meetings were arranged with (I had planned a subreport listing company names and dates of the meetings. The input form already has a CompanyID field and the user fills in the Meeting Date field on the form they are using)

*Materials sent (On the user's form, there is a subform bound to the second table. My assumption again was that I would use a subreport to pull the data from this second table)

*A list of those prospects who were moved to a category of declined or dropped (we rate potential investors on their likelihood of investing. Essentially they are rated on a 1 through 6 numbering system with declined/dropped being the 7th option. On the user's form there is a place to note that this investor should be moved to this category and thus searching for those moved to this category is an easy search. Again, I felt a subreport would be the easiest way to list these companies.)

My single largest problem is the number of times I keep getting asked for the beginning and ending dates for which to include records. Using parameter queries as the basis for each report, every subreport generates a new request for the beginning and ending dates for my range. I do not, of course, ask for the date range in every underlying query, but those queries do in some fashion refer back to a query that does ask for the beginning and ending dates. And each time a new query/subreport refers back to the initial query with the date range question, the program again asks for the range.

About the only thing that comes to mind is to use a temporary table to hold the record numbers that I want to use for this report. That is, run a query that selects all record numbers within the requested date range. Then let all the queries support my report refer to this temporary table, limiting the report to applicable records. Then, when the report is done, empty the table. But this would require at least a little bit of code and I was trying to avoid using any. At first glance, it also seems like a complex way to do something that feels like it should be easy.

So, If I have
1. a query to count phone calls
2. a query to count meetings set up
3. a query to pull up a list of materials sent
4. a query to pull up a list of meetings arranged
5. a query to list all companies moved to the declined/dropped category

How do I avoid having every one of these ask me what the date range is for my data to be reported? I do not see how to do this report without subreports; and once I have multiple queries/subreports, I do not see how to avoid being asked repeatedly what date range to use for selecting data.

P.S. Are you old enough to recognize the initials with the quote at the end of my last message or just decide it was too tangential to think about? Probably you are too young to have the the phrase of bless your pea pickin' little heart.

 

[JoeMiller]

The suggestion I gave before which obviously wasn't clear enough is to make a form to call the report and place two text boxes on it to represent your start date and your end date. The user opens this form to produce the report, enters the dates they are interested in and the main query that supplies all the data (the one with your popup parameters currently) gets modified to look at the form for it's dates instead of asking over and over.

So let's say you make an unbound form called [frmReports] and you put two unbound controls on it [txtStartDate] and [txtEndDate]. These will make up your date range. There should be a command button I assume for the user to press which will print the report.

Now we need to modify the query to look at frmReports for it's dates whenever it is run. So go into the query, and in the criteria where you currently have the parameter (IE: [Enter Date:]) change it to this:

Between [Forms]![frmReports]![txtStartDate] and [Forms]![frmReports]![txtEndDate]

When that query is run, it will go to frmReports to find it's dates. Instead of popping up an input box every time it needs the dates, it goes to the form again and again. Hopefully that is clearer now?

PS - Yes, at the ripe old age of 25 I don't believe I'm old enough to recognize the initials or the pea pickin' heart thing. Sorry!! Joe Miller
joe.miller@flotech.net

 

[rjmccafferty]

I was being dense. I had it in my head that even with a form for the dates, i still had a parameter query and therefore the problem would still exist because all the queries would re-run the parameter quesitons. But, duh, yes they will all ask question, but they will ask it of the form, not via user input.

Back in the 50's, maybe a bit into the 60's there was a singer (but also did some comedy routines and acting) name "Tennessee" Ernie Ford. His biggest hit as a singer was 16 tons. When he did comedy, it was somewhat like Gomer Pyle with the pretend southern, hick, as shucks type of stuff (please don't tell me you don't know who Gomer Pyle is). In any case, picture Gomer trying to be nice but also being unbelievably naive about life, trying to say thank you to someone, and the line is "Well, bless your pea pickin' little heart" Ask your parents, they'll remember.

I have children older than you; geez, I have computers older than you! Almost.

Anyway, thank you very, very much for your patient assistance.

 

[rjmccafferty]

I have one last (hopefully) question. When I got to using the Dcount function, as long as I knew the variable I was after, it worked fine. But when I tried to use is with a like statement, it gave me an error message.

Can you tell me what is wrong with this syntax for a dcount field:

=DCount("[ActivityType]","RmCallInput","[ActivityType] = like 'CM*' &quot

When I used a like statement in a query and looked at the SQL view in Access, this is the format it seemed to use. Is my syntax wrong or do I need to look for a different reason for the error message? (The field result is "#error&quot

Thank you.

 

[JoeMiller]

Remove the = sign after ActivityType, you don't need it. Also make sure that the name of the textbox is not ActivityType or RmCallInput, it's probably not, just covering all the bases.

Joe Miller
joe.miller@flotech.net

 

[orman]

Here is a very simplistic method of counting in report. Add a text box to the header (or footer) of a section you want to count. Go into the properties of the text box, and then the "Data" tab. In the "Control Source", enter the formula "=1" (no quotes). In "Running Sum" select "Over All". This will number each occurance in the report. The last occurance is the total count!