Expression quandry

J

JohnE

I am creating a report from scratch as it will be mainly a
simple report showing various statistics from the database
tables. A few examples are: total number of clients,
total number active clients, total revenue, etc. Most all
of this info comes from different tables. I have
considered doing a query(ies) but that became to
cumbersome. So, I would like to place in the control
source of the textboxes an expression. However, I am not
doing it right, not even with the expression bldr.
I seek the guidance of someone to get me started correctly
in the right direction by showing me the correct
expression.

table is usrtblClientInformation
field is ClientCode
field is Status

I would like to count the total number of clients (less
the demos).

Another example is same as above but for only the number
of active clients.

Push gently in the right direction. And thanks.
*** John
 
M

Marshall Barton

JohnE said:
I am creating a report from scratch as it will be mainly a
simple report showing various statistics from the database
tables. A few examples are: total number of clients,
total number active clients, total revenue, etc. Most all
of this info comes from different tables. I have
considered doing a query(ies) but that became to
cumbersome. So, I would like to place in the control
source of the textboxes an expression. However, I am not
doing it right, not even with the expression bldr.
I seek the guidance of someone to get me started correctly
in the right direction by showing me the correct
expression.

table is usrtblClientInformation
field is ClientCode
field is Status

I would like to count the total number of clients (less
the demos).

Another example is same as above but for only the number
of active clients.
Click to expand...

You can use the Domain Aggregate functions to do that:

Total number of clients:
=DCount("*", "usrtblClientInformation","Status<>'Demo'")

Total number of active clients:
=DCount("*","usrtblClientInformation","Status='Active'")

Total revenue for active clients:
=DSum("amount","usrtblClientInformation","Status='Active'")
 
J

JohnE

Marsh, thanks for the push (or should I say kick in the !
@#. I have done this before but today I had a complete
brain dead day. Probably brain freeze from the weather.
Thanks again.
*** John
 
J

JohnE

Marsh, hope you see this as I have a question on same
subject. How is the expression done if seeking
information but there were 2 tables (or more) to bring
together for the statistic. Using the info in my original
message and to get the number of Contacts from the
usrtblContacts table for each active client. The
identifier among the tables is the ClientCode.
*** John
 
M

Marshall Barton

JohnE said:
Marsh, hope you see this as I have a question on same
subject. How is the expression done if seeking
information but there were 2 tables (or more) to bring
together for the statistic. Using the info in my original
message and to get the number of Contacts from the
usrtblContacts table for each active client. The
identifier among the tables is the ClientCode.
Click to expand...

Ceate a saved query that Joins the two tables on their
ClientCode field. You can then use the query as if it were
a denormalized table.
 
Top