Is there another way?

[deejay_totoro]

Hello all,

This is not really a problem - but rather Im wondering if there is an alternate solution.

My goal is to work out a percentage, for example, the amount of data submitted by each region as a percentage.

To do this I must:
1: calculate the total number of region entries held on the database. I do this by creating a 1 column query. Column one uses the region field, and total is set to "count". This returns the total number of region entries.

So now I know how many region entries there are in the database.

2: Next, I create a 2 column query. Column one uses the region field, and total is set to "group by". Column two is set to "region" field, but total is "count." This returns the total number of entries for each region.

So now I know: the total number of region entries and the number of entries for each region.

So to work out the percentage I would:

* Create a new query
* Add the query that holds the totals for each region

Then use the following expression:

percentage: ([NumberOfEntriesPerRegion])/(DLookUp("[MyQryThatHoldsTheTotalNumnerOfEntryInfo]","[MyFieldThatHoldsTheTotal]"))

This seems a very long winded way to find out the percentage. Hence the reason for this post! Is there a more straightforward way?

Thanks!

dj_T

 

[uncle-lai]

One query will do:

Region - Group By
Entries: Region - Count
Pcntge: [RegionOfCount]/DCount("*","YourTableName")*100
- Expression
Field Format set to Percentage

Peter

 

[deejay_totoro]

cant get it

Hello

Thanks for the reply.

I tried to use your suggestion - but to no avail.

Could you maybe explain it for me?

That would be great!

Thanks.

dj_T

 

[uncle-lai]

In your 2 column query (as mentioned in your first post) add a third column, in the first row enter this line in whole

Percent: [RegionOfCount]/DCount("*","YourTableName")

Total is set to Expression
In Field Property set the Format to Percentage

Note: "YourTableName" means the name of your data table with qotes.

Run the query.

Peter