I need your help in access how to count the duplicated during certain period

Table One : Unique Values

Table Two : Contain the unique value and duplicated Value

Number  Close Time
1110    01-11-22 8:47
1110    02-11-22 8:47
1110    03-11-22 8:47
1111    02-11-22 8:47
1111    05-11-22 8:47
1111    06-12-22 8:47
1112    03-11-22 8:47
1112    08-12-22 8:47
1112    09-12-22 8:47

i need 2 types of result first one include the value im looking for like this

Result  Count
1110    3
1111    2
1112    1

and the second type exclude the value im looking for

Result  Count
1110    2
1111    1
1112    0

i can do it in excel using =countifs but now im working on huge data ( millions of records ) and most of the data sent on access files

enter image description here

i dont know why tables became like this so i added a photo

CodePudding user response：

Should be doable with a GROUP BY and COUNT(*):

SELECT Number, COUNT(*) 
FROM tableTwo
GROUP BY Number, CloseTime

For the second result, just put COUNT(*)-1.

CodePudding user response：

Perhaps this query will suit you

SELECT t3.Num1 AS Num, sum(inResult1) AS CountInclude, sum(inResult2) AS CountExclude
FROM 
 (SELECT t1.Number as Num1,t1.CloseTime as CloseTime1,t1.MaxDate
   ,t2.Number as Num2,t2.CloseTime as CloseTime2
   , iif(t2.CloseTime >= t1.CloseTime and t2.CloseTime<=t1.MaxDate,1,0) AS inResult1
   , iif(t2.CloseTime >  t1.CloseTime and t2.CloseTime<=t1.MaxDate,1,0) AS inResult2 
  FROM tableOne AS t1 INNER JOIN tableTwo AS t2 ON t1.Number = t2.Number
 )  AS T3
GROUP BY T3.Num1;

You may write "LEFT JOIN", this will be better.