Hi i am trying to gain a percentage from values i recive from a query (table values attachment displays values from query) as you can see from the image award name BAC has 7 male, 32 no, and 3 females. i would like this to show as a percentage instead. I am using a access query to get the values in the image "table values" .i will be putting the values into a report. The current SQL i am using to get the values show in the image is. - SELECT Student.Gender, Count(Student.Gender) AS CountOfGender, Awards.AwardName
-
FROM Student INNER JOIN Awards ON Student.AwardID = Awards.AwardID
-
GROUP BY Student.Gender, Awards.AwardName;
Any help will be greatly appricated, also if any more info is needed please let me no.
Thank you 7 1883
You can use a query to get the count by group and then join that into your existing query so you have all the variables you need to calculate the percentage.
hi thanks for the reply. i am new to access and everything that goes with it. By what you say do you mean create a new query in access and then somehow link them together. or add more code to the existing query to make it do what i am hoping?
thanks again
Basically - SELECT Group, Individual, COUNT(*) / GroupCount AS Percentage
-
FROM someTable AS a
-
INNER JOIN (
-
SELECT Group, COUNT(*) AS GroupCount
-
FROM someTable
-
GROUP BY Group
-
) AS b
-
ON a.Group = b.Group
-
GROUP BY Group, Individual
hi thanks again for the quick reply i have changed you code to the appropriate values and still run up with a error. the error is at the bottom. - SELECT Awards.AwardName, Gender, COUNT(*) / GroupCount AS Percentage
-
FROM student, Awards AS a
-
INNER JOIN (
-
SELECT Awards.AwardName, COUNT(*) AS GroupCount
-
FROM student, Awards
-
GROUP BY Awards.AwardName
-
) AS b
-
ON a.AwardName = b.Awards.AwardName
-
GROUP BY Awards.AwardName, Gender
Looks like you'll have to split out the count into its own subquery and then join everything back together.
NeoPa 32,556
Expert Mod 16PB
Sorry 315, but that's not remotely similar to Rabbit's SQL. You've introduced an extra table to create a cartesion product (See SQL JOINs). You cannot expect such changed SQL to work in a similar way.
You could also uses the DCount("Field","TableName","Criteria") to retrieve the value to calculate your percentage from the base table, as well.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rob Meade |
last post by:
Hi all,
I have a recordset iterating through and dumping out to the screen a series
of percentages, using the precision 5 and numericscale 2 etc.
When I dump them to the page some of the...
|
by: gudia |
last post by:
Two tables:
T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 is
the primary key.
T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factor
numeric(18,2))--x1 is the...
|
by: Horst Rotenholzer |
last post by:
Hello,
I have a question on Netscape 6.2 (6.x?). I use CSS and divs for
positioning different elements, but N6.2 doesn´t do the right thing.
N6.2 ignores the percentage values, but not e.g....
|
by: Les Juby |
last post by:
I am trying to adjust the window/table size of a website
(www.worklaw.co.za) which has made use of DIV tags with its settings
embedded in an CSS file.
The client wants its width and height to...
|
by: thomas goodwin |
last post by:
I have a query which asks for a parameter value to execute it.
To see the results I have to:
a) click on the query -- the "Enter Parameter Value" window pops up.
b) enter the parameter value
c)...
|
by: jez123456 |
last post by:
Hi
Is there anyway to display the percentage value completed in a Progressbar.
I've found a Microsoft link
http://support.microsoft.com/default.aspx/kb/323116/EN-US/
to create a smooth...
|
by: vstolmech513 |
last post by:
Here goes nothing...
I'm trying to make a report that shows what guys work on what crew, ie. day crew or night crew. For this selection I've got a form with a combo box with either a "D" or "N"...
|
by: natalie99 |
last post by:
Hi All :)
I am trying to stipulate rules for the return of a pricing value in Access.
I know this should be very easy, I simply cannot seem to make my expressions work!
The two tables have...
|
by: Aswanth |
last post by:
I'm Using Asp.Net with C# & Working with SSRS 2005 for Generating Reports..
The Following Expression I'm using in Reports to Show the Percentage of Particular Items in REPORT..
...
|
by: de_ja |
last post by:
Hi,
If, for example an access database that concerns insurance claim
investigations.
The database comprises only one table containing data - investigation
reference number, referral date,...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |