The query returns the parts used for each job. This could be 1 to many, so I get 1 to many rows returned in the query for each job. There can be a number of jobs to a work schedule (I'll call this WS). Each job is for a particular model. So I bascially have
My database is set up to track call evaluations with 4 fields for number data (S, A/C, C/E and B) each of these have a possible point total. I also have a percentage field to track out of total possible points.When I run my query I get a list of each of the totals for each of the evaluations with the associates names (as expected).I take that query and try to run a report wizard to give me an average socre for each associate. and the system returns averages of 0 or an odd number that does not make sense.when I use the =Avg([fieldname]) process I get an accurate average of the total but can not get it to do a "subtotal" for each associate.
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.
I need the total of days in a report but exclude the repeated ones.
So user are working sometimes in different work orders on the same day but our administration only needs to know the number of days worked in one period of time.
i send a jpg with the example i use the =Nz(Count([Date Worked]),0) but that way i get all the entries counted
how to come up with the number of unic days worked as the DB i atached if worker works in diferent work order in the same day in the report it comes up as he work two days
I have an unbound field in a report that i'm trying to do the following:
=Count(IIf([Total Build Time]<=15,1,Null))
This gets me the closest to my answer. Tried it in many different variants, this will get me the total number of records, but will not use my criteria (<=15). The "total build time" field is formatted as short time 00:00 and i have tried my criteria in the same way...plus many more. This will count the records but will not limit the result to 15 or less.
I am migrating a database from Spreadsheets to Access 2010. Everything else is going well but I am stuck at one point. A table has Name, Gender, and Nationality fields. Now, I need a report that will have only Nationality and Gender fields. The nationalities will be in a list and another column should have total count for each nationality. Then, the Grand total should be print at the bottom of the report. Moreover, two other columns should have a count of each gender (male and female) against every nationality.I need Report which will have Four columns i.e. Nationalities, Total, Male, Female... The nationalities column will contain a list of nationalities that are there in the data table [field name: National].. The total column will count and show the sum of each nationality from the data table [same field: national]... The Male and Female columns will do the same i.e. count the occurrence and show the total for Male and Female from the data table.
SELECT qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, Sum(qryHoursReq.Total_Time) AS SumOfTotal_Time, qryHoursReq.S_date FROM qryHoursReq GROUP BY qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, qryHoursReq.S_date HAVING (((qryHoursReq.S_date) Between [startdate] And [enddate]));
This is my query
I'm trying to get a count of distinct volunteerID in an unbound textbox on report.
If I use =Count(*) I get 2 when I put in date parameters and it groups by program
This is kind of correct, but, these 2 are the same person, she had different days she participated.
What code can I use for only get a count of 1
FOrgot to mention, when I use the DCount with numerous examples, I get #Error
Report has records of errors. There are 3 types of error. In the report footer, I would like a total count (this is working!) and a count by type (can't get this to work).
Error Total =Count[ErrorType] Compliance Total =Sum(IIf([ErrorType]="Compliance",1,0)) Audit Total =Sum(IIf([ErrorType]="Audit",1,0)) Quality Total =Sum(IIf([ErrorType]="Quality",1,0))
I have tried setting groupings on the report but this alters the detail sorting (currently sent by the date field newest to oldest) and provides the count within the detail.
In Access 2003 I have a form, at the footer of which I want to put some summary information. One of which is a count of the number of purchases over £500. I have tried many variations after searching the posts on this site but the only one which does not return me an error of some kind with the expression is
=IIf([Bought For]>"500",Count([Bought For]),0)
where 'Bought For is the reports listing of the purchase prices in a given date range when opening the report, but the value returned in my test is '0' when it should be '2' as the test file has two purchases of over £500.
I am trying to do a Count query and can't get a 0 to appear in the count result. Pretty week on code so searches on Nz and IIf have made me more confused than ever
Any help appreciated
Query is
SELECT Count([6qryExpWar1_1].aid) AS CountOfaid, [6qryExpWar1_1].EW1 FROM 6qryExpWar1_1 GROUP BY [6qryExpWar1_1].EW1;
Hello, I am new to Access and trying to run a query that will count only unique invoice numbers in my table. I am sure this is an easy command, and thank you for your help!
I want to do a unique count of dates when an activity was done in my table. The table may have multiple entries of the activity performed possibly on the same date by an individual
totV = ECount("[dateAssessed]", "R_P_Data_P", "[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' AND [Activity] Like '*audit*'") totV = Unique count dateAssessed = date field in R_P_Data_P table R_P_Data_P = table "[approvalNoSys] = '" & [Forms]![cmrOverview]![txtappNoSys] & "' = criteria for the customer in question to separate them from many other customers in the table. Activity = text field in R_P_Data_P table audit = the activity
I'm also trying to avoid having to build total queries etc to them reference them, I'd like getting the desired outcome in an expression or small code.
I read about Ecount but my complier doesn't recognise the function
I have 2 linked tables, tblPN & tblReceivedDate. tblPN has field PN and tblReceivedDate has field [Received Date]. The tables are used to record the receipt of different part numbers and the date they received. I want to use a query to count how many times a part number is received. The catch is that I only want to count a part once even if it is received more than once on the same date. With the data in the attached DB the count for PN 123 would be 5.how to configure the query to do what I need to do?
I am trying to produce statistics reports in my database. The user selects the information they require in a form (date from, date to etc). A query then produces the results listing each job in turn which is what I want however at the end I would like to produce sub-totals for each client.
Depending on the criteria selected by the user, only some clients will appear in the list. Ideally I would like the footer to show something like:
Client 1 - 4 jobs Client 2 - 1 job Client 5 - 6 jobs Client 6 - 17 jobs
assignment_num field: 1001, 1002, 1003, 1004, 1005 project_num field: 20,20,15,16,15 employee_name field: tom peter, tom peter, tom peter, julie, sally
What I want is to display the employee name with 2 projects or more...
What i have done? I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..
I am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.
SELECT t1.* FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date]) WHERE t2.OverrideDescription IS NULL;
However I also need to be able to do a unique count of the [OverrideDescription] field.
I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".
Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy FROM tblCurrencies ORDER BY tblCurrencies.Ccy;
Now - I have a separate table of balances, which is linked to the currency table by the same CcyID, and which also has identifiers to link it to other tables (e.g. AccountID)
As part of the above query, I want to return the count of unique AccountID's in the balance table for each currency. So in other words, I want to know, for each currency, how many unique accounts exist?
Each AccountID could appear one or more times in the balance table (one-to-many relationship), so I only want to count the number of unique ID's.
So I started with the following :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tblBalances.AccountID) AS NoOfAccounts FROM tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy ORDER BY tblCurrencies.Ccy;
But this just gives the number of AccountID's per currency (regardless of duplication within them)
I found this article which informs me that a Count(Distinct) query won't work in Access and to use subqueries instead.
Code: Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Set db = CurrentDb strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN
[Code] ....
The result of the query is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"...
Code: ' count records in query Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Dim beginDatum As String Dim eindDatum As String Set db = CurrentDb
