Expression quandry

  • Thread starter Thread starter JohnE
  • Start date Start date
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
 
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'")
 
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
 
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
 
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.
 
Back
Top