Calculating percentages of number of occurence of a text value

[TonyWilliams via AccessMonster.com]

I have a field that is called txtpurpose. The value of this field in any
record could be 4 different values " Re-financing", "Corporate Finance", "Buy-
out", or "Restructuring" For a particular month (a field txtmonth) I want to
count how many records had each of these values and what percentage that
number was of the total number or records for that month. So I want to end up
with a reuslt like this:
Re-financing 46%
Corporate Finance 38%
Buy-out 12%
Restructuring 4%
Total 100%

Can someone start me off in the right direction please?
Many thanks
Tony

 

[Jeff Boyce]

Tony

A Totals query would give you a way to determine a Count of each
[txtpurpose] {use GroupBy} in a given [txtmonth] {a selection criterion}.
You could then use a second query based on the results of the first to
calculate percentages.

JOPO (just one person's opinion)

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[TonyWilliams via AccessMonster.com]

Thanks Jeff I'll try that
Tony

Tony

A Totals query would give you a way to determine a Count of each
[txtpurpose] {use GroupBy} in a given [txtmonth] {a selection criterion}.
You could then use a second query based on the results of the first to
calculate percentages.

JOPO (just one person's opinion)

I have a field that is called txtpurpose. The value of this field in any
record could be 4 different values " Re-financing", "Corporate Finance",

[quoted text clipped - 14 lines]

Many thanks
Tony

 

[TonyWilliams via AccessMonster.com]

This seems to work:
SELECT tblhvdealspt1.txtsector, Count(tblhvdealspt1.ID) AS CountOfID,
tblhvdealspt1.txtmonth
FROM tblhvdealspt1
WHERE (((tblhvdealspt1.txtsector)="Transport" Or (tblhvdealspt1.txtsector)
="Other" Or (tblhvdealspt1.txtsector)="Manufacturing" Or (tblhvdealspt1.
txtsector)="Distribution" Or (tblhvdealspt1.txtsector)="Services" Or
(tblhvdealspt1.txtsector)="Wholesale"))
GROUP BY tblhvdealspt1.txtsector, tblhvdealspt1.txtmonth
HAVING (((tblhvdealspt1.txtmonth)=#6/1/2009#));

Thanks for the starting point.
Tony

Tony

A Totals query would give you a way to determine a Count of each
[txtpurpose] {use GroupBy} in a given [txtmonth] {a selection criterion}.
You could then use a second query based on the results of the first to
calculate percentages.

JOPO (just one person's opinion)

I have a field that is called txtpurpose. The value of this field in any
record could be 4 different values " Re-financing", "Corporate Finance",

[quoted text clipped - 14 lines]

Many thanks
Tony