Calculated field question????

Hello:
Thanks for reading this post.

I need to create a metrics (form or report - I don't care which) to
display calculated fields about the database (A2002 front end to SQL
Server 2K)

1) I can't seem to enter SQL straight into the control

2) I tried entering things like (in the control) =COUNT([STDVerifyBy] =
'Test') --- and other variants.... with no results


So, Question. Can you build a calculated control on a form or report
that will let you gather and display statistics like....

- count of all records where STDVerifyBy equals "Test"
- count of all records where TesterID is NOT NULL
etc., etc., etc.


No real database info to be displayed, just a page of metrics data
gathered from querying the db.

Any ideas would be greatly appreciated....

Richard Hollingsworth

re: Calculated field question????

Seems a fairly straight forward question, however I may have misinterpreted it.

countif, and sumif are probably what you are looking for.
this should be in the "Control Source" property of the field.
Countif works along the lines of:
=countif([primarykey],"([STDVerifyBy] = 'Test')")
Sumif works along the same lines.

You can't put these in the PAGE footer, only in the report footer, or other footers. I think if you put it in the detail, you will get a count of either 1 or 0 depending on whether the particular detail record meets the criteria.

Hope that helps!

Regards,

Fraser.

Richard Holliingsworth <william.r.hollingsworth@boeing.com> wrote:[color=blue]
>Hello:
>Thanks for reading this post.
>
>I need to create a metrics (form or report - I don't care which) to
>display calculated fields about the database (A2002 front end to SQL
>Server 2K)
>
>1) I can't seem to enter SQL straight into the control
>
>2) I tried entering things like (in the control) =COUNT([STDVerifyBy] =
>'Test') --- and other variants.... with no results
>
>
>So, Question. Can you build a calculated control on a form or report
>that will let you gather and display statistics like....
>
> - count of all records where STDVerifyBy equals "Test"
> - count of all records where TesterID is NOT NULL
>etc., etc., etc.
>
>
>No real database info to be displayed, just a page of metrics data
>gathered from querying the db.
>
>Any ideas would be greatly appreciated....
>
>Richard Hollingsworth
>[/color]

re: Calculated field question????

I believe that CountIf and SumIf are Excel functions. Check out DCount and
DSum, I believe they will do what you want.

--
Wayne Morgan
MS Access MVP


"Richard Holliingsworth" <william.r.hollingsworth@boeing.com> wrote in
message news:Hs9wt1.DEG@news.boeing.com...[color=blue]
> Hello:
> Thanks for reading this post.
>
> I need to create a metrics (form or report - I don't care which) to
> display calculated fields about the database (A2002 front end to SQL
> Server 2K)
>
> 1) I can't seem to enter SQL straight into the control
>
> 2) I tried entering things like (in the control) =COUNT([STDVerifyBy] =
> 'Test') --- and other variants.... with no results
>
>
> So, Question. Can you build a calculated control on a form or report
> that will let you gather and display statistics like....
>
> - count of all records where STDVerifyBy equals "Test"
> - count of all records where TesterID is NOT NULL
> etc., etc., etc.
>
>
> No real database info to be displayed, just a page of metrics data
> gathered from querying the db.
>
> Any ideas would be greatly appreciated....
>
> Richard Hollingsworth
>[/color]

re: Calculated field question????

Hi folks.

Thanks for the help, but I can't seem to find either set of commands in
the Access 2K help file. Where should I look for these commands????


Thanks,
Richard H

Wayne Morgan wrote:
[color=blue]
>I believe that CountIf and SumIf are Excel functions. Check out DCount and
>DSum, I believe they will do what you want.
>
>
>[/color]

re: Calculated field question????

You will find them in the VBA help file. You have to open a code window
(Alt+F11) then open help.

Here are a couple of short excerpts from the help file:

You can use the DCount function to determine the number of records that are
in a specified set of records (a domain). Use the DCount function in Visual
Basic, a macro, a query expression, or a calculated control.

For example, you could use the DCount function in a module to return the
number of records in an Orders table that correspond to orders placed on a
particular date.

DCount(expr, domain, [criteria])


You can use the DSum functions to calculate the sum of a set of values in a
specified set of records (a domain). Use the DSum function in Visual Basic,
a macro, a query expression, or a calculated control.

For example, you could use the DSum function in a calculated field
expression in a query to calculate the total sales made by a particular
employee over a period of time. Or you could use the DSum function in a
calculated control to display a running sum of sales for a particular
product.

DSum(expr, domain, [criteria])

These functions are refered to as "domain Aggregate Functions". The
remaining similar functions are:

DAvg
DLookup
DFirst, DLast
DMin, DMax
DStDev, DStDevP
DVar, DVarP

--
Wayne Morgan
MS Access MVP


"Richard Holliingsworth" <william.r.hollingsworth@boeing.com> wrote in
message news:HsBF9s.KJt@news.boeing.com...[color=blue]
> Hi folks.
>
> Thanks for the help, but I can't seem to find either set of commands in
> the Access 2K help file. Where should I look for these commands????
>
>
> Thanks,
> Richard H
>
> Wayne Morgan wrote:
>[color=green]
> >I believe that CountIf and SumIf are Excel functions. Check out DCount[/color][/color]
and[color=blue][color=green]
> >DSum, I believe they will do what you want.
> >
> >
> >[/color]
>[/color]

re: Calculated field question????

Richard Holliingsworth <william.r.hollingsworth@boeing.com> wrote in message news:<HsBF9s.KJt@news.boeing.com>...[color=blue]
> Hi folks.
>
> Thanks for the help, but I can't seem to find either set of commands in
> the Access 2K help file. Where should I look for these commands????
>
>
> Thanks,
> Richard H[/color]

Richard,
Did you look in the Expression Builder after creating a new query?
Scroll down to functions in the leftmost window, then choose the
Built-In Functions folder under that. In the middle column, choose
"Domain Aggregate" and then DSUM and DCOUNT will show on the rightmost
window/column.