Counting Values In A Field
Feb 28, 2008
Hey, I'm new to microsoft access, and I could do with a little help please :) . On a database I have created, I have a table with the field "Results" in it. This field has been set up in the Lookup properties to be a choice from "Win" "Loss" or "Draw". My question is, what is the expression I would use to count the total number of records in my form with "Win" selected?
I tried to set up the DCount expression, but this gave me some odd results.
Currently my database is set up like this,
TBL_Match_Results:
Fields:
GameNumber (Autonumber + Primary key)
Date
Result (With the choices above)
Then I made a continuous form from this table, and on the Form Footer I had a text box with the value,
=DCount([GameNumber],"TBL_Match_Results",[Result]="Win")
I had hoped this would simply give me the total number of records with a win in them. However, insted the value changes depending on which record I have selected on the form. It is "10" if the selected record is a win, or "0" if it is not. I'm not sure if the problem is the poorly written expression (I'm not 100% sure how to work them), or if I'm even using the right expression (I was only told to use DCount). If anyone could shed some light on this, I would greatly appreciate it!
EDIT: I've just realised where the 10 is coming from, it's the number of records in my table, when i added a new one it changed to 11.
View Replies
ADVERTISEMENT
Feb 28, 2008
Hey, I'm new to microsoft access, and I could do with a little help please . On a database I have created, I have a table with the field "Results" in it. This field has been set up in the Lookup properties to be a choice from "Win" "Loss" or "Draw". My question is, what is the expression I would use to count the total number of records in my form with "Win" selected?
I tried to set up the DCount expression, but this gave me some odd results.
Currently my database is set up like this,
TBL_Match_Results:
Fields:GameNumber (Autonumber + Primary key)DateResult (With the choices above)
Then I made a continuous form from this table, and on the Form Footer I had a text box with the value,
=DCount([GameNumber],"TBL_Match_Results",[Result]="Win")
I had hoped this would simply give me the total number of records with a win in them. However, insted the value changes depending on which record I have selected on the form. It is "10" if the selected record is a win, or "0" if it is not. I'm not sure if the problem is the poorly written expression (I'm not 100% sure how to work them), or if I'm even using the right expression (I was only told to use DCount). If anyone could shed some light on this, I would greatly appreciate it!
EDIT: I've just realised where the 10 is coming from, it's the number of records in my table, when i added a new one it changed to 11.
View 1 Replies
View Related
Nov 5, 2012
I need to count the number of entries for a particular item located in a table. To keep it simple, we have a database used to process orders for Turkeys, this database has a field for each order called "Long legged Weight". In order to sort the turkeys in the shed, we need to know how many of each weight there are.
Order Number / 10-12 / 11-13 / 12-14 / 13-15 / (etc)
......1 ................1
......2 ..........................1
......3 .....................................1
......4 ..........................1
Total ..................1 ......2 .........1
View 14 Replies
View Related
Feb 2, 2006
Hi I have a table that looks like this:
Col1 Col2 Col3 Col4 Col5 Col6
1 A B C D E
2 A B C D F
3 A B C G H
4 A B J K L
5 A D M N P
Does anyone know how I can run an SQL query to count how many letters there are For example : 5 A's 4 B's 3 D's Etc
Cheers,
bikeboardsurf
View 2 Replies
View Related
Jul 16, 2005
MY QUESTION:
I’m using Access 2002.
I’m trying to create a query that will COUNT the number of values within 1 hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that I need to have every hour bin represented whether there is any value in it or not. I believe that I need to create a separate table of the bins that I want (include bin start-value & stop-values) and then bring this table into my query and join it to the original table.
I was able to construct the make table query below to COUNT the values in hour bins for which there were records, but I also need rows for all of the zero values;
SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS [TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins]
FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter
GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter, [All NWHI Detections].Island, [All NWHI Detections].Location, DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI Detections.Date]), [All NWHI Detections].Date, [All NWHI Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time])
HAVING ((([All NWHI Detections].Species)="tiger"))
ORDER BY [All NWHI Detections].Transmitter;
Q1? Creating the hour bins table
This table will be very large because it will need to have 4 years worth of hour bins for each transmitter (N=15) and location (N=12). This results in 6,307,200 hour bins! How can I write a query to create this table, or is there a better way of doing this?
Q2? Bringing the hour bins table into the main query.
How do I include the hour bins table in the main query to get my final result.
Any help would be greatly appreciated! Thank you.
View 1 Replies
View Related
Nov 26, 2007
Hi All, I have read a few posts on here but can't quite get a solution to my particular issue.
I have two tables in a query:
tbl_suppliers
tbl_supplier_perf
tbl_suppliers is right joined with tbl_supplier_perf by
[Location Name]----->[Supplier]
No as part of tbl_supplier_perf there is a YES/NO checkbox, where it can be ticked if there is an issue with a supplier delivery. This field is called [Issue?].
I want to report all suppliers (not just those with records in tbl_supplier_perf) with a count of the amount of records created in tbl_supplier_perf with a tick in [Issue?]. So if no records in tbl_supplier_perf have [Issue?] ticked it will just report 0.
Basically the query needs to report all suppliers with a count of how many records have been ticked "YES". It is a check box so I believe they are recorded as 0 and -1.
I believe I need to use Dcount but I do not know how to get that in to my existing query!
View 9 Replies
View Related
Dec 9, 2014
As part of the report I'd like to have a table that would list all different values in column in column A and the number of times they appear in the table in column B.I have a bit of a hard time how to populate the table the easiest way.I have all the values in a separate source table, so first column is easier. but do i have to write the dcount statement for each row for second column?Could I display results of query as subquery in report.If you have a bunch of textboxes with increasing names like NameBox1... for for/next loops in reports... is there an easy way to assign those names?
View 9 Replies
View Related
Nov 29, 2007
Hi, I searched the forum for this but the only thread that came close to what I was looking for was this.
http://www.access-programmers.co.uk/forums/showthread.php?t=125240&highlight=null+chart
Basically I have chart in a report thats based on a query that counts the amount of entries per month between two dates inputted by the user.
It all works fine but the chart that is based on the query only shows months that have an entry.
Eg if it counts all dates between the two dates and say the only month that has an entry is July, the chart will only show July. What I want is the other months to show (Null values) as zero, so every month shows. I'm probably missing something basic but can anyone help?
[TextPriDate] is the start date
[TextPriDate2] is the end date
This is the query code (QryDate)
SELECT
tblMain.ID1, tblMain.Dt
FROM
tblMain
WHERE
(((tblMain.Dt) Between [Forms]![frmSwitchboard]![TextPriDate] And [Forms]![frmSwitchboard]![TextPriDate2]));
This the code from the chart in the Report
SELECT
(Format([Dt],"MMM 'YY")) AS Expr1, Count(*) AS [Count]
FROM
QryDate
GROUP BY
(Format([Dt],"MMM 'YY")), (Year([Dt])*12+Month([Dt])-1);
Thanks
View 2 Replies
View Related
Jan 25, 2015
I have a question regarding counting of text values base on their status and using that result to a calculation.
Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.
What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.
View 2 Replies
View Related
May 10, 2014
Suppose we have a report that outlines several fields, one field shows the City. In the report, this week, we see 10 records "New York", 8 records "Houston", 7 records "London", 3 records "Paris" and so on.
Next week's report outlines different cities and different number of records. I need to have in the report footer a "recapitulation" , a field that would say :
New York 10
Houston 8
London 7
Paris 3
Total 28
Next week cities and number of records might not be the same, we may have
Tokyo 12
Singapore 14
New York 6
London 7
Can i make my report in that way that it will count the values without using VBA ? I tried the count values option but it counts the overall report, does not take into consideration the different values.
View 2 Replies
View Related
Sep 15, 2014
I am a novice and have been searching to no avail for a solution to my problem. I have a main form and a subform (datasheet view) of a table. In my main form I have various calculations which calculates the data from the subform (when auto-filtered). What I am trying to do is count the distinct [Call Number] reflecting the data from the subform into a textbox in the main form (when subform is auto-filtered).
View 14 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
Oct 4, 2013
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;
Crosstab query did the trick..
View 1 Replies
View Related
Mar 15, 2007
I am working on a report that has a text field with "YES" or "NO" in it. This is not a yes/no field. Is there a way to count the number of "YES"'s and "NO"'s? There will also be some records that will not have anything listed. See example below.
Failed Patients
Item 1 - YES
Item 2 - NO
ITEM 3 - NO
ITEM 4 -
ITEM 5 - YES
ITEM 6 -
Total Failed Patients - 4
View 2 Replies
View Related
Mar 5, 2012
I have a query where one field is a calculated field dividing one field summed by another field summed. But when looking at the data in excel, I figured out that one of the fields was adding a fraction onto the number when I summed up the whole column including the field name leading me to believe that Access was doing this as well.
View 14 Replies
View Related
Dec 4, 2011
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name
1 xx yy KPP USA John
2 zz kk KPP USA John
3 ss ff TLL USA John
4 ww qq PPO Italy Marco
5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT
COUNT(DISTINCT department) AS NumberOfDifferentDepartments
FROM people
GROUP BY name;
But Access says me there is a syntax error.
I'm working with MS Access 2002.
View 2 Replies
View Related
Mar 20, 2015
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center
Job Type
Hours Paid
Pay Code
Earnings Amount
20530
Security Guard II
7.5
REG
190.37
[Code] .....
View 14 Replies
View Related
Nov 23, 2005
hi there
I'm building a forum from the scrath. It is already working, but I want to have in each topic the number of replies it has.
there are 2 tables, one for the topics and other to the replies. in the replies table there is a field called id_post that has the id of the post witch it belongs.
I solve the problem by inserting a query inside the loop of the posts:
<%
While Not rs.EOF
%>
<%
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT COUNT(*) as cont FROM replies where id_post= " & rs("id") & " "
rs2.Open sql,Conn,1,2
%>
post: <%=rs("post")%> replies:<%=rs2("cont")%>
<%
rs.MoveNext
wend
rs.close%>
but someone told that this would make the page slower, so I want to know if I can do the same thing without having the query inside the loop.
I don't know if I made myself clear, sorry for that.
tks for the help.
View 1 Replies
View Related
May 10, 2013
I am trying to build a calculated field that counts the number of times the letter E appears in 8 fields. the query currently looks like the attachment.
I need to have one more field , lets call it NetFlags, that is the number of times the Letter E is in the row for each Technician.
So for Brown, Tom NetFlags = 2, for White, Paul NetFlags = 4 and Wills,Fred = 0
View 1 Replies
View Related
Oct 16, 2013
I am looking to do a calculated field. Not sure what is the best way to do this, but I would like to have a dates in a table with all of our employees that has what date they started with the company. From that I want to know not how many days they have been with the company, but just the years.
So if I have been with the company 364 days, it should only show up as 0, and if I have been with the company for 365, it will read 1. I would like this to be in a query somehow. We use the number of years they have been with the company for their pay, and need it to be years, and not days.
View 9 Replies
View Related
Oct 2, 2013
I would like to count a field if another is empty. I have 2 fields "registered" and "Started" and "closed"I would like to count all the fields "registered" if the field "started" is empty. I also want to do it with "started" if "closed" is empty.
View 2 Replies
View Related
Jul 19, 2014
I have a report which is based on a query. The query combines information from TBLDwgReg and TBLDwgRegDtls. The unique field linking these two tables is DrawingNo. The query - QRYDtldDwgReg - is showing 99% of the information I want to see.
My problem is this - one drawing can be revised several times. I have my report grouped by IssuedBy and then grouped by DrawingNo. In the group footer for IssuedBy I want to count the number of drawings issued by a particular consultant. For example...the structural engineer has issued 17 drawings for a particular project but when I do a count it is returning a total 27 because some of those drawings have been revised.
I tried grouping in the query and counting the DrawingNo field there but that's not working either.
View 14 Replies
View Related
Aug 22, 2015
there are 3 options Yes Somewhat and Not really I want to count the records that only have Yes.
View 5 Replies
View Related
Jun 29, 2012
The below formula is counting the records that have null fields in the InspDate. What is wrong in the syntax?
=[YrInpDueG0]-Sum(IIf([FSL]=0,IIf(Not IsNull([Insp_Date]),1,0)))
View 2 Replies
View Related
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
