Queries :: Determine Gender Ratio Of Associations Membership - Percentage Occurrence
Apr 28, 2014
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL
FROM [Mail List]
WHERE ((([Mail List].[GENDER])="F"))
GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
View Replies
ADVERTISEMENT
Nov 17, 2013
I have a database with two tables. One for customers, one for the books that they buy.
I have a field for people's titles (Mr, Mrs, Miss, Ms) and I'd like to make a graph in Excell to show the relationship between gender and amount spent on books.
I'd like to just have two fields, male and female to put into Excell so the graph makes more sense than all four titles and then an explanation that Mrs, Miss and Ms must be added together to compare the total spent with the males.
How can I do this?
View 1 Replies
View Related
Mar 20, 2013
I have a simple query like below.
Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
View 3 Replies
View Related
Feb 13, 2014
I'm having trouble creating a query to give me a count of all the males with a certain ethnicity and females with a certain ethnicity. I have a table that is setup for those two categories. It has the ethnicity and gender listed with check boxes (requested by the user) and when ever I run the query with the count total, for example : Caucasian Males, it returns all Caucasians whether male or female. I've tried a few different ways to get this done, but with no success.
Here is a screenshot of my table :
View 2 Replies
View Related
Jan 12, 2015
Here are the 2 tables that I am working with:
Account
Account
0041980041
0051670051
0052330052
0053490053
Conversion
Account Conversion
0041 10000151
0051 10000152
0052 10000153
0053 100001524
I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.
View 4 Replies
View Related
May 8, 2013
I've made a membership database for an imaginary leisure centre as part of my A Level coursework - only after more or less finishing my project, I've realised that I haven't provided a way for the end users to calculate fees for members.
I suppose the calculation I would have to do is multiply the Length of Membership (days) field on the Membership Opportunities by Cost per Month on the MembershipTypes table.
These are the relevant tables and I've also attached my database (the password is "password" for any of the users) ...
View 1 Replies
View Related
Nov 25, 2014
I have a table with name, club members details ID etc. This is linked by a 1 to many link (ID) to a table containing details of membership subscription payments. One entry/row per membership period. This second table has DatePaid, Paid (Yes/No), Period. Period contains 2013-14, 2014-15 etc.
I can do a query for those that have Paid (Yes) but when I try one for those that have not Paid (No) or <>Yes I get no result. I only enter members when they have paid. I need a query to display those who have not paid for the 2014-15 period.
I can do it a long winded way copying 2 lists into Excel. One all members. One those who have paid. Then remove duplicates and those paid in 2013-14 leaves those not paid in 2014-15.
View 2 Replies
View Related
Mar 5, 2015
I would like to have a field in a report show the ratio of two numbers i.e. New Customers is 20 and Lost Customers is 24, and have the ratio field to show 5:6 ratio. I know how to do it in an Excel Spreadsheet (=B1/GCD(B1,B2)&":"&B2/GCD(B1,B2), B1 is new customer and B2 is Lost Customer, but I have not been able to figure out how to make it work in an Access report. I tried to do it in a query, I've tried this code I found on the forum, but could not make is work for my problem.
Public Function CalcGCD(OneNumber As Long, OtherNumber As Long) As Long
Dim lngSmallest As Long
Dim lngGCD As Long
Dim i As Long
lngSmallest = IIf(OneNumber < OtherNumber, OneNumber, OtherNumber)
For i = lngSmallest To 1 Step -1
[Code]...
View 2 Replies
View Related
Jul 8, 2015
For example, I have 1 table with 3 fields : Name, Birth date, Gender. Then I want to create a report where all data are grouped by age range and gender
View 1 Replies
View Related
Jul 2, 2015
All of the types? Including Memo?
View 2 Replies
View Related
Jan 29, 2014
I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
View 3 Replies
View Related
Jul 18, 2013
Using Access 2000
I have a field which lists a date. I would like to determine which financial month the date is.
However the financial month starts and ends at odd times, for example
The start time of the month is the first Sunday after the last Friday of each month. With the end date being the day before this.
So for July it would have been
30/06/2013 - 03/08/2013
and August is
04/08/2013 - 31/08/2013
I would like the output to show something like "July 2013"
How to do this?
View 4 Replies
View Related
Jan 26, 2014
I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
View 12 Replies
View Related
Jun 28, 2013
I have a query that displays the average call rate on various pieces of equipment over the last 12 months. I was wondering if there is a way to have Access look at this query and determine if the call rates are trending up or down over that time frame. Kind of like a trend line would do on a chart in Excel, only I want this to show me the word Up or Down in my results. Is this possible?
Here is the query I am using:
Code:
TRANSFORM FormatNumber(Avg([All Call Rate]),2) AS AvgCallRate
SELECT List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
FROM List_With_TNC_tbl
GROUP BY List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
PIVOT Format([Month Start Date],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun", "Jul","Aug","Sep", "Oct", "Nov", "Dec");
View 3 Replies
View Related
Jun 12, 2006
Dear all,
I have an equipment database, within which I am attempting to create associations between various items. The items are distinguished as level 1, 2 or 3 depending upon where they sit in the equipment hierarchy, level 2 being sub-equipments of level 1, and level 3 being sub-levels of 2.
So for example the level 1 item could be PC, level 2 items would be keyboard, mouse, monitor, base unit etc.
What is the best way to structure this relationship? I had considered generating a sub form, and have set up queries to generate lists of potential items with each level description - so that users can select the items they want to add to the associated list of equipment, but I'm not sure how/where to generate that associated equipment list.
Hope this makes sense?
Thanks in advance for any help.
Steve
View 3 Replies
View Related
Aug 15, 2007
I have about 40 queries based on employed trainees from different groups(MMF, MKO, CNP,...). Now, what I'm trying to do is find the percentage of trainees employed from the total trainees, % employed in community from total trainees, % employed at wuskwatim from total trainees,... and so on.
If you take a look at my DB, you will find I have have MANY queries made. Is there an easier way to find this kind of information?
Any suggestions would be appreciated.
18448
View 3 Replies
View Related
Jun 20, 2005
I just recently figured out how to count how many jobs a CSR has submitted and how many errors a CSR has made. What I need to do now is combine these two queries and figure the percentage of jobs correct that a CSR has submitted. I tried to combine these to queries in a report and figure it out that way, but that didn't work. When I combined the two queries I lost the individual information in the queries. I tried to combine the two queries but it wouldn't let me. It said that I was trying to combine the fields of a table and a query and that I should use one or the other.
Can anyone help me?
learnasugo
The excel file is how they would like the report to look
View 9 Replies
View Related
Nov 7, 2013
Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View 3 Replies
View Related
Dec 11, 2014
I'm new to this and I've created a report that shows evaluation scores before and after for some training we are delivering. The Query I've created works out the percentage of change for each record. The problem I'm having is that I can't now summarize these percentages for each instructor.
What I'm entering to retrieve the percentage score is:
=(Sum(IIf(IsNumeric([Percentage Change]),[Percentage Change],0)))/(Sum(IIf(IsNumeric([Percentage Change]),1,0)))
What i get back is as follows:
Before score = 2.85 (correct)
After Score = 4.22 (correct)
Percentage of Change = 55% (incorrect this should be 48% )
View 14 Replies
View Related
Apr 16, 2013
How to calculate the percentage of kilometers per order compared to the total number of kilometers per carrier.
In the attached screenshot send queries where I need formula.
Mileage on the orders of the field called "Korkma" . The formula should look like: # [Korkm] / [total kilometers per carrier] #.
I do not know how to get the total mileage driven by the carrier (the carrier is in the query in the Field Name Plate) for the month.
For example:
date 1.1.2013.,Plate:Pribus Korkm= 37
date 2.1.2013.,Plate: Pribus,Korkm=56
date 10.1.2013.,Plate: Pribus, Korkm= 70
Formula: [37]/[163] = 0,23
[56]/[163]= 0,34
[70]/[163]= 0,43
View 1 Replies
View Related
Jun 11, 2013
I have a query that has clients sorted in the row area and a column from my table that is set up as text but only has yes/no options for the data. I have a pivot table that counts the yes's and counts the no's and finds a grand total; however, I want to be able to find the percentage of pass's given the grand total. I'm able to do this in Excel and was wondering if it was possible in Access.
View 2 Replies
View Related
Oct 25, 2013
I'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand.
Here are two examples:
[URL] ....
[URL] .....
I believe I need to create a query based on another query (which I can do). I would need to create a column in the new query that calculates the percentage difference, but what's the formula/contents of that column?
View 5 Replies
View Related
Oct 15, 2013
I have a query that calculates a percentage that works fine. The sql is like so:
SELECT Table1.cost, Table1.sales, Table1.location, [sales]-[cost] AS GrossProfit, [GrossProfit]/[sales] AS GrossProfitPC
FROM Table1;
If I try to set criteria to select records based on the calculated value of the expression (eg; only select records with a GrossProfit percentage > 50) when I run the query it asks for a parameter as if the field [GrossProfit ] were a parameter.
Is there a way i can set a criteria based on the value of the percentage field?
View 2 Replies
View Related
Aug 5, 2014
I am trying to set up a calculation between two values to show the percentage difference. In Excel, for example, I would have two values, £905,175 and £891,563, and I would enter =A1-G2)/ABS(A1), which would then return a plus or minus percentage value. how to do this in a query using Access 2010?
View 3 Replies
View Related
Jun 17, 2015
I am trying to make a query that give me an output of an aggregate percentage column, or running sum of percentage. Please see my example:
Month Sales in % Running sum percentage
Jan 5.03 % 5.03 %
Feb 2.17 % 7.20 %
Mars 1.28 % 8.48 %
I have the column Month and Sales in %, What do I have to do to get an output like the example over?
View 2 Replies
View Related
May 6, 2015
I have a very simple query which brings back a count of records. I would like to add a percentage column to this which shows a percentage by ClinicCode. I've attached some sample data and what the inteded outcome should be.
View 1 Replies
View Related
