Percentage of inactive customers

[Antonio]

Hello, all...
I have a table (CustomerDB) with a CustomerID as the
primary key and a checkbox for the inactive customers.
What I would like to do is get the percentage of inactive
customers ((inactive customer / customerid)*100). Can
somebody help me with the coding? Thanks, Antonio

 

[Wayne Morgan]

First, you have to determine who qualifies as an "inactive" customer. Do you
have a date field or something to indicate when they last purchased in order
to qualify as inactive? You would then divide the count of these customers
from the count of all the customers and multiply by 100.

Something like this may work:
sglInactivePercentage = DCount("[CustomerID]", "tblCustomers",
"[LastPurchaseDate]<=#12/31/2003#") / Dcount("[CustomerID]", "tblCustomers")
* 100

You may want to not multiply by 100, but leave the number in decimal form
and just format it as a percent when you display it.

 

[Erik Oosterop]

Antonio,

Try this query:

Select TOP 1 (SELECT Count(CustomerID) AS CountOfCustomerID
FROM CustomerDB WHERE Inactive=Yes)/(SELECT Count(CustomerID) AS
CountOfCustomerID
FROM CustomerDB) as PercentageInactive FROM CustomerDB


Two notes:
- I am assuming that your checkmark for inactive is called "Inactive" and
that this field is of type Yes/No
- Maybe someone in this newsgroup knows a preferred alternative to using the
TOP 1 clause near the start.

Let me know if this works for you,
Regards