Queries :: Creating Query To Count Gender And Ethnicity?
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 Replies
ADVERTISEMENT
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
Mar 22, 2006
Hi everyone!
I am creating a database for a local Food Bank and the director needs a report to show a breakdown of ethnicity...
His Board of Trustees need to see how many Caucasian, African-Americans, latinos, etc. used the service in a months time.
For the purposes of this email, I want to ignore the date requirement and focus on how to create a query that differentiates the ethnicities and counts them individualy. So rather than creating a query that displays ALL the AA, L, and C... I want it to show a total number for each category.
Is this even possible?
It would probably be easier for me to use a check box for ethnicity, but I'm trying to save space on the form
If anyone has a suggestion I'd be most appreciative!
Thanks!
View 10 Replies
View Related
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
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 2 Replies
View Related
Apr 14, 2014
I am using an existing database which allows my company to track claims information. One report my supervisor has asked for is a table which shows the counts of each kind of event occurring at a district level by department. For example, the classifications are injury classes like FSA, so I need to design a report which says facility a has 3 fsa's in the Wireline department in a table format. I have been looking into union queries as suggested by other sites but nothing seems to work. My labels are District, Analysis Code and Department.
View 3 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Apr 27, 2015
I have recently been working on a database that is meant to be quite user friendly. It contains two tables, one that lists 'trainees' and another that lists 'job applications' in a 'one to many' type relationship. It is designed to monitor Job applications being made by people doing courses here. So far, I have created a very practical form for data entry. I've created a QBF to allow the user to filter the Job Applications quite substantially. I'm trying to build a form that allows the same criteria to be entered, but that counts the number of Job Applications, and then creates a pie chart that subdivides the applications by 'source of application' (i.e. Indeed.com, Family, Newspaper'), or Industry of the potential employer.
For example, I would like to be able to select a programme or course that someone is on, their starting date, a date range for the application and some other details- i.e. Select an Industry then view a pie charts for the source of those applications and vice versa.
I've created a form for this, with a button for 'Source' and one for 'Industry' the two different pie chart types I would like to be able to select. I have created two separate query by forms for each, with the buttons linking to the respective Macro. The problem I'm having is that the query seems to ignore the WHEN criteria completely.
SELECT COUNT([Job Applications].[Application ID]), [Job Applications].source
FROM [Job Applications] INNER JOIN Trainees ON [Job Applications].[Trainee ID] = Trainees.[Trainee ID]
WHERE (
[Job Applications].[Application Date] > forms!CustomChart!DateAfter OR
forms!CustomChart!DateAfter IS NULL) AND
[code]...
View 3 Replies
View Related
Oct 16, 2013
I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:
Code:
SELECT
[MainTbl].Location,
Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod],
Count([Tbl1].[Agent Name]) AS [NoCompleted],
Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]
[Code] .....
But I get the following results:
Code:
Location EvaluationPeriod NoCompleted NoPassed
Location1 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location1 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location1 Month1 Tbl3.NoCompleted Tbl3.NoPassed
Location2 Month1 Tbl1.NoCompleted Tbl1.NoPassed
Location2 Month1 Tbl2.NoCompleted Tbl2.NoPassed
Location2 Month1 Tbl3.NoCompleted Tbl3.NoPassed
etc..
View 4 Replies
View Related
Nov 5, 2013
I have TableA with
CityCode (indexed, no duplicates, Primary Key)
CityType (duplicates ok)
TableB, with
CityCode (indexed, duplicates OK)
StreetCode (indexed, No Duplicates, Primary Key)
A One to Many relation between Tables on City Code
In need to feed a cell table with the value of the number of streets that are in "Type1" cities
View 5 Replies
View Related
Feb 7, 2006
:eek: I'm running a Make table query that links two tables in a one to many relationship. What I need to do is add a column that is a running or sequencial counter. The counter is used to preserve the order of the child records thus I don't want it sorted. It would be nice if the counter would reset when the Parent-ID changes. This use to be quite simple in Access 98...so much for better ideas. I have reviewed the other forums, but most deal with totals, I need running count. The others are unclear, so I asking one more time. Thanks
Sample of what I need:
Parent-Id_ Child-Id_ Count
___ 123___ 043____ 1
___ 123___ 121____ 2
___ 123___ 321____ 3
___ 123___ 065____ 4
___ 243___ 032____ 1
___ 243___ 043____ 2
___ 243___ 012____ 3
___ 187___ 321____ 1
___ 187___ 022____ 2
View 1 Replies
View Related
Jul 17, 2014
I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:
SELECT Count([summary].Key) AS CountKey
FROM Summary, lookup
WHERE ([Region])) Between [Region 1] And [Region 2]));
View 4 Replies
View Related
Jan 7, 2014
I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.
View 13 Replies
View Related
May 28, 2014
If I have a table called "Calls" with an autonumber and another field with names for stores like this:
1 Hobby Mart
2 Peter's Store
3 Hobby lobby
4 Hobby Mart
5 Peter's Store
How can I build a query to count the number of different store names?
For example, in this case I need the query to return the number 3.
View 7 Replies
View Related
Jul 20, 2013
i have a access table (AUTH) with following field
Company Auth Held
Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
i used the following query to calculate the Held from a table named pers.
SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth
then i got the result as under
Company Auth Held
Tata 12 02
Dell 11 08
HP 21 20
Opera 11 12
Bangour 10 12
now i want the difference between Auth and Held as Sur/Defi
how can be it done.
View 2 Replies
View Related
Feb 2, 2015
I have an access query named "leaveapp" and I want a running count as below:
EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1
and i need a column in the right with running count like below
EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1
View 3 Replies
View Related
Jun 5, 2014
I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.
View 4 Replies
View Related
Sep 16, 2013
iwhen i add a field to allow me to add the parameters for a search between to dates. the query will only count the ethnicity of people who complained on the same day and give the others as a single number. i need to allow the user to search between dates as to generate the data for a report.
View 3 Replies
View Related
Oct 24, 2014
I have a query that gives a value in a column either "A","V","L","H","P","S" or blank.
I want to be able to count the amount of occurrences of each letter and hold that number in a column. Is it easy to do?
View 5 Replies
View Related
May 21, 2013
For small tables (<10,000 rows), which technique is faster, more efficient ?
View 11 Replies
View Related
May 3, 2013
I'm trying to do a query to count corresponding records in another table. It works except for returning zeros. I've tried using NZ and switching the type of join, but to no avail.
Here's what I have:
Query A has 3 columns (FU kids)
AlphaID
DtcCtr (a Location Code)
DlsDtc (a Date of change)
Table A has many columns, but I'm only using a few. (dbo_MNCPSTNote)
AlphaID
DtcCtr (the same Location Code)
ServDate (the date I'm trying to count)
Here's what I have:
SELECT [FU kids].AlphaID, [FU kids].DtcCtr, Count(dbo_MNCPSTNote.ServDate) AS CountOfServDate
FROM dbo_MNCPSTNote RIGHT JOIN [FU kids] ON dbo_MNCPSTNote.AlphaID = [FU kids].AlphaID
WHERE (((dbo_MNCPSTNote.Center)=[FU kids]![DtcCtr])) OR (((dbo_MNCPSTNote.ServDate)>[FU kids]![DlsDtc]))
GROUP BY [FU kids].AlphaID, [FU kids].DtcCtr;
I want to know the count, including zero, of the number of records based on ServDate for each AlphaID in Query A.
I didn't create the tables and have no control over how they are designed/organized.
View 6 Replies
View Related
Sep 19, 2014
I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
View 14 Replies
View Related
May 11, 2014
I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..
So far this thread has been very useful: [URL] ....
It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).
In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").
The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])
So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.
Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.
View 5 Replies
View Related
Apr 19, 2013
I am trying to create a query that matches two other querys and finds records that are in the primary, but not the secondary query. I have tried to use the query wizard for this, but it will not work for me the way I want.
If I have A,B,C,D in one query and only have A,B,D in the other I want to find C, so that I can format an email funtion in my database using that data.
View 1 Replies
View Related
Feb 11, 2014
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id
1 MISC 2013-10
2 MISC 2013-10A MISC 2013-10
3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum
FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID
WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
View 1 Replies
View Related
Apr 27, 2015
Is there a way to have Access send an alert message when a particular date is getting close? I have a database that tracks automatic step increases to pay and when the dates occur. I would like to create a pop up that could alert me when a date is getting close so that I can inform the budget person that a certain employee will be getting a bump in their pay starting on a particular date? Is that possible in 2010?
View 3 Replies
View Related
