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 have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time End Date/Time Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee 12/06/2014 01:00--12/06/2014 03:00------John Smith 12/06/2014 04:00--12/06/2014 06:00------Jane Doe 13/06/2014 02:00--13/06/2014 05:00------John Smith 13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example: table1 01/01/2014 15/01/2014 17/01/2014 30/01/2014
Explanation: Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates) Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2 Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 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.
I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.
Code: SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount FROM qryNoticeResponseNew GROUP BY qryNoticeResponseNew.fldNoticeID;
Which is just counting the number of dates so far. It got me to thinking I need to do something like this.
Code: SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount FROM qryNoticeResponseNew GROUP BY qryNoticeResponseNew.fldNoticeID;
Which pops a "cannot have aggregate function in expression" error.
I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.
I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.
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!
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..
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
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
here goes im trying to count the number in a group between a time period. the query takes a time period from a form runs then reports who and how many heres what i get
SELECT tblEstimate.Client, Count(tblEstimate.Recieved) AS MyCount FROM tblEstimate GROUP BY tblEstimate.Client, tblEstimate.Recieved HAVING (((tblEstimate.Recieved)>=[Forms]![frmEnquireFront]![EqfromDate] And (tblEstimate.Recieved)<=[Forms]![frmEnquireFront]![EnqtoDate])) ORDER BY tblEstimate.Client;
I think this might be a typical question for query builders, so I apologize in advance for asking something so basic.
I have a form with two controls (start_date) and (finish_date).
Is there a way that I can create a query that will count the number of times a "source" has been entered into a table?
For example, I have a database where potential customers call and ask about our services. We ask them "Where did you hear about us?", hence the "source" field (which is a drop down combo box to normalize that field's data). With this record is their "dateofcall" which is (obviously) a date field.
I'd like to create a report that will count the number of times a "source" has been entered between two dates "dateofcall" (the start and finish date above).
I have tried many types of queries and haven't found any success. The nice thing about the two form controls is that I can use those two controls for a variety of all types of queried reports. (the user enters a start and a finish date, fires a command button that generates a given report between those two dates). And it works well!
I would like to count only dates where the Close Date is less than or equal to the Target Date. Here is what I have done, but it does not work. any ideas how I might get this to work?
SELECT A.[SCA_Open Issue Tbl.ID] AS [IDCount], A.[Criticality of Risk] AS [Criticality of Risk], A.[Database] As [Database], A.[Risk_assumed_without_mitigation] As [Risk Assumed WO Mitigation], A.[OTS] As [OTS], A.[Issue Closed Date] As [Close Date], A.[Targeted Completion Date] As [Target Date] FROM [EOY Closed SC&A Issues] A
UNION ALL SELECT B.[RefID] AS IDCount, B.[complexity_desc] AS [Criticality of Risk], B.[Database] As [Database], 0 As [Risk Assumed WO Mitigation], B.[OTS] As [OTS], B.[EndDate] As [Close Date], B.[TargetEndDate] As [Target Date] FROM [EOY Closed Risk Assessments] B
WHERE [Close Date] BETWEEN Date() <= [Target Date];
I created a query and one of the fields in it is for dates. I need to create a report that will only count how many entries have dates and it shouldn't count those with no/blank dates .
Is there a way to put a criteria in my query for date field? What would be the formula? Or is there a formula that I can put straight to my report that will only count the ones with dates?