help...please...brain...going smooth...

[nuhmmie]

I'm posting in the general forum for I have no idea where my 'issue' belongs...

I have created a database in Access 2000 that lists a certain type of customer accounts. Four of the fields are for option groups with "yes/No" on the form, translated into "1/2" values in the table. I wanted to be able to calculate the percentage of records with the "yes" value as the data accumulates/updates. But....I can't figure out how!

the skinny:
8k + records
contract in file? :yes or no
contract signed?: yes or no
contract dates match?: yes or no
contract terms match?: yes or no

questions the boss would like to see answered :
what's the percentage of contracts in the file?
what's the percentage of contracts signed?
what's the percentage of dates that match?
what's the percentage of terms that match?

each percentage being updated as the record is updated.

Am I dreaming? Am I lying to myself? Please understand, I spent the past three days teaching myself Access 2000, so, if my issue is a cinchy, forgive me. LOL

 

[sfreeman@co.mer]

Try this....

Any pecentage calculation should simply be the total number of items at issue divided into the number of those items that meet your criteria.

In other words, if you have 8000 total records, and there are 5000 with contracts, then the percentage is simply:

5000 divided by 8000, or 0.625 or 62.5%.

Easily done in a query.

Assuming a table called tblOne, with field ID (autonumber) and Contracts (Yes/No).

SELECT
Count(tblOne.ID)

AS
CountOfID
, tblOne.Contract
, (SELECT Count(tblOne.ID)

AS
CountOfID FROM tblOne

AS

TotalNumber
, [CountOfID]/[TotalNumber]

AS
Percentage

FROM
tblOne

GROUP BY
tblOne.Contract

HAVING (((tblOne.Contract)=True));

OK, play with this and you will probably be able to shorten it a bit.

HTH