473,554 Members | 3,299 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

calculating a percent of total in a query

I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to write the
correct expression due to the "groupby" needed to group the city count
in the first column. Any clues?

Nov 13 '05 #1
2 35872
mh*****@comcast .net wrote:
I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to write the
correct expression due to the "groupby" needed to group the city count
in the first column. Any clues?


Do you really need to have the percent calculated when running the query
or can you create a column with the total counts of cities and when you
run your process code on the resulting recordset calculate the percent?

For example:
Expr1 : Dcount("CityId" ,"Table","crite ria")

Then when you want to process, in a form or module calc the %?

To make it faster than using DCount, you could create a TotalsQuery that
gets the count of cities. Since the Totals query would return 1 record,
you could do a Cartesian join. Simply add that query to the other
tables (we'll call it citycnt) and then drag that count field into the
column next to the City field. Don't set a relationship. THen run.

If you absolutely need to have a percentage returned when the query is
first executed instead of being calculated, you could run Dcounts() on
the specific city divided by the total counts of cities.


Nov 13 '05 #2
Salad wrote:
mh*****@comcast .net wrote:
I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to write the correct expression due to the "groupby" needed to group the city count in the first column. Any clues?

Do you really need to have the percent calculated when running the

query or can you create a column with the total counts of cities and when you run your process code on the resulting recordset calculate the percent?
For example:
Expr1 : Dcount("CityId" ,"Table","crite ria")

Then when you want to process, in a form or module calc the %?

To make it faster than using DCount, you could create a TotalsQuery that gets the count of cities. Since the Totals query would return 1 record, you could do a Cartesian join. Simply add that query to the other
tables (we'll call it citycnt) and then drag that count field into the column next to the City field. Don't set a relationship. THen run.

If you absolutely need to have a percentage returned when the query is first executed instead of being calculated, you could run Dcounts() on the specific city divided by the total counts of cities.


thanks...I essentially did your suggestion of joining a totals query
with the speicific city query...also, I found that if I renamed the
columns, I can use them in an expression for a new, calculated column!
That is, Expr1:City is my first column (which contains the groupedby
City count data, i.e. the number of each City), Expr2:TotalCity Count is
my second column (from the totals query), then the third column is
Expr3:100*Expr1/Expr2 which gives the percent.

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
2375
by: | last post by:
I have an Access database used to track donor pledges. In it, there is a table that contains three fields for each donor: Gift_Amount, Gift_Per_Year, and Matching_Gift_Ratio. The following formula would calculate the total pledge amount for each donor: (Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1). A total Pledge for all...
4
4837
by: The Bit Bandit | last post by:
Hopefully someone can help me create a query that I'm having some trouble with. I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo
1
2503
by: Megan | last post by:
quick summary: i'm having problems trying to group fields in a report in order to calculate percentages. to calculate percentages, i'm comparing the results from my grouped fields to the totals. first, let me say that this is a really long post. i wasn't sure how much information/ background to provide, so i thought more was better than...
3
2697
by: luscus | last post by:
Thanks for all the responses on my first question. Unfortunately the answers I was given were too complicated for my small brain , and neophite condition to understand. So if you could talk down to me and write in easier terms I would be very gratefull to all you access wizards! Here is my problem. I have a table with maybe 10 fields, It is...
39
3044
by: VidTheKid | last post by:
THE PROBLEM The % symbol is too vague when defining dimensions in CSS and HTML. It can relate to an inherited value, a measure of the containing element (which can differ between box models) or for background image placement, the size of the element minus the size of the image. A SOLUTION There should be new %-like units defined in CSS to...
5
9409
by: jonm4102 | last post by:
I'm trying to calculate the median of some numerical data. The data can only be found in a query (henceforth query 1) field I previously made, and I would prefer to calculate the median in a new query it without making a table out of query 1. I can't find a median function in the "Total" field, so is there so way to make an expression to...
2
16992
by: dath | last post by:
Hi, Not really a programmer here, but have been forced into the role. I was asked to develop a basic time sheet for employees to enter time. I developed the Table without a problem. I then developed a query to limit the records displayed based upon an employee last name and a month. Once again no problem. I then stareted developing the...
5
4277
lee123
by: lee123 | last post by:
hi there i cant figure out how to get a percentage from a form i have created maybe one of you people can help me. the form i have has all of this on it: date,new orders,new, used,replacement orders,new contract,short in bundles, total,total new, used total,total percent. i have copied and pasted a (totalpercent) textbox on the form...
4
4835
by: zoeb | last post by:
Hi. I have a form which the user enters 2 years worth of data into (one record per year). The aim, is to populate the table this form is based on with 3 more years worth of data (i.e. creating 3 new records), based on a percentage increase on the previous year. This form is based directly on a table called tblBudgetShareProj. So far I have...
0
7519
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7798
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8039
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7887
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6140
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5152
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3556
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1130
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
838
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.