This one is 2-fold

[kavarin]

I have 2 questions..so I will divide them inot 2 separate posts:

I use a query as the source for a report. This query includes a field that has multiple possibilities for the value (selected from a separate linked table). I want to count the # of instances that a specific value comes up and have a total on the report.
Example:
I have 15 records that this query shows. 5 of them have "In Process" as this field's value...5 have "completed", etc.
I want to be able to count all the "completed" values, without modifying the rest of the report.

Is this possible, or have I lost everyone on this explanation? Any thought would be appreciated

 

[David R]

Look into using Count(). If your report is grouped by the values you're talking about this will be easy; use the Group Header or Footer and put a text box = Count(*).

If they're not grouped already then you'll have to use Dcount("FieldName","TableName/QueryName","FieldName = '" [IntendedValue,PossiblyFromAForm] & "'"), modifying as appropriate. Make sure you put it in a Report Footer/Header so that it doesn't get counted over and over again.

HTH,
David R

 

[kavarin]

The test field that I created to try this (which doesn't work) hehehe is in the report footer..so that part I understand, but I hate to say that your answer is somewhat confusing to me (and I apologize)
The query DOES NOT use this field as the first selector to find its records. This field is one of the required fields from the filtered records that we require reports on. It is the 4th or 5th colum in my report, but the values are different for each record. I am looking to count only the instances of a certain value within that column.
I am trying to think of a way to have you view the report and query with out emailing the entire DB. Please let me know your thoughts.
Thanks

 

[David R]

Did you try the Dcount version in your footer? It will be slower but should work.

DCount can be a little arcane at times, but basically what you are doing is listing DCount("what field to count","what table or query to count it in","how do you determine whether to count this record or not"). In your case, the third part (WHERE clause) would be to determine that the field is actually equal to the value you want.

An alternative way is to use a small query and JUST include that field, twice. Change it to a Totals query, make the first field "Where" and put in Criteria the value you want. The second field change to "Count".
Then you can put a box refering to that query's one result in your report footer. =DLookUp("CountOfFieldName","[QueryName]")

HTH,
David R


[This message has been edited by David R (edited 04-17-2002).]

 

[kavarin]

Would it be possible to email you to discuss this further? If you are like me, then you are slave to some 9 to 5 grind, so I know you are busy.....but your guidance could really be valuable to me. Anything you could do would be great.
I am at dcowlin@haywardcapital.com

Thanks David,
David (go figure)

 

[David R]

I would rather you posted topics to the forum, not only because my time is cramped (I was out of the office all day today, for example), but because you will receive a wider variety of responses from people possibly MUCH more knowledgeable than I.

Post a new topic if it's something sufficiently different from where you started, or just continue here (since your topic line is a bit ambiguous, I would start a new topic and summarize where you're at).

Good luck,
David R

 

[kavarin]

Not a problem at all. I know EXACTLLY where you are coming from on that one.

I did post a new topic regarding page breaks in large reports...if you have the time perhaps you could throw your 2 cents in on that.

Thanks again for your time