Access Query Counter Per Group

Group By and Count in Access

SELECT SOURCE_ID, COUNT (*) AS VEHICLES_WITH_AC
FROM VEHICLE
WHERE VEH_AC = 'Y' AND VEH_YEAR < '2008'
GROUP BY  SOURCE_ID;

1) You apparently require totals by source_id and nothing else, so you don't group by anything else.

2) your condition on VEH_YEAR should go in the where clause otherwise all veh_year rows will be selected and thrown away after aggregation.

Access query counter per group

On a real RDBMS, one would typically use the window function ROW_NUMBER for this.

select *
, row_number() over (partition by Group1 order by Value, ID) as Rownum
from yourtable

But an alternative is to use a correlated subquery.

select *, 
(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value
        or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum
from yourtable t

Extra:

Simple tests to show the difference between ROW_NUMBER, RANK and DENSE_RANK

create table yourtable (
 ID int identity(1,1) primary key, 
 Group1 int, 
 Value int
);

insert into yourtable (Group1, Value) values
  (10,100),(10,150),(10,150),(10,150),(10,360)
, (200,360),(200,420),(200,420),(200,500),(200,500)

--
-- ROW_NUMBER (to get a sequence per group) 
--
select *
, ROW_NUMBER() OVER (PARTITION BY Group1 ORDER BY Value, ID) as Rownum
from yourtable
order by Group1, Rownum

ID | Group1 | Value | Rownum
-: | -----: | ----: | -----:
 1 |     10 |   100 |      1
 2 |     10 |   150 |      2
 3 |     10 |   150 |      3
 4 |     10 |   150 |      4
 5 |     10 |   360 |      5
 6 |    200 |   360 |      1
 7 |    200 |   420 |      2
 8 |    200 |   420 |      3
 9 |    200 |   500 |      4
10 |    200 |   500 |      5

--
-- Emulating ROW_NUMBER via a correlated sub-query
--
select *, 
(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value 
        or (t2.Value = t.Value 
            and t2.ID <= t.ID))
) as Rownum
from yourtable t
order by Group1, Rownum

ID | Group1 | Value | Rownum
-: | -----: | ----: | -----:
 1 |     10 |   100 |      1
 2 |     10 |   150 |      2
 3 |     10 |   150 |      3
 4 |     10 |   150 |      4
 5 |     10 |   360 |      5
 6 |    200 |   360 |      1
 7 |    200 |   420 |      2
 8 |    200 |   420 |      3
 9 |    200 |   500 |      4
10 |    200 |   500 |      5

--
-- RANK (same values get same rank, but with gaps)
--
select *
, RANK() over (partition by Group1 order by Value) as Ranknum
from yourtable
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       5
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       4
10 |    200 |   500 |       4

--
-- Emulating RANK via a correlated sub-query
--
select *, 
(select count(t2.value)+1 from yourtable t2 
 where t2.Group1 = t.Group1
   and t2.Value < t.Value) as Ranknum
from yourtable t
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       5
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       4
10 |    200 |   500 |       4

--
-- DENSE_RANK (same values get same rank, without gaps)
--
select *
, DENSE_RANK() over (partition by Group1 order by Value) as Ranknum
from yourtable
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       3
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       3
10 |    200 |   500 |       3

--
-- Emulating DENSE_RANK via a correlated sub-query
--
select *, 
(select count(distinct t2.Value) from yourtable t2 
 where t2.Group1 = t.Group1
   and t2.Value <= t.Value
) as Ranknum
from yourtable t
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       3
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       3
10 |    200 |   500 |       3

db<>fiddle here

Counting/grouping records in Access query

This is how aggregation and GROUP BY work.

If you group by date, you will get the count per date.

If you want to only group by employee to get the full count, then do that. Remove V_LOCATION_VISIT_DATE from the GROUP BY clause.

Which date would you want to get with the full count? The first? The last? Then use MIN() or MAX() aggregate functions for the date.

---

Edit from comment:

Then the date goes into the WHERE clause, like this:

SELECT qryEmployees.EmployeeName, Count(*) AS EmpCount
FROM (Data_Servisi INNER JOIN qryEmployees ON Data_Servisi.WORKER = qryEmployees.EmployeeName) INNER JOIN City ON qryEmployees.CityID = City.CityID
WHERE Data_Servisi.V_LOCATION_VISIT_DATE BETWEEN Forms!myForm!DateStart AND Forms!myForm!DateEnd
GROUP BY qryEmployees.EmployeeName

How to group by and count based on a condition

One way to do this is to do conditional aggregation using the sum and iif functions:

select 
  category, 
  sum(iif(status='Open',1,0)) as Open,
  sum(iif(status='Closed',1,0)) as Closed,
from table
group by category

MS Access 2007 SQL Query: Nested (COUNT, GROUP BY) in SELECT statement

A Subquery does work in Access (at least Access 2013), I know it's a "single query to rule them all" approach, but it does work...

    SELECT P.Expedite, P.DueDate, P.Underwriter, P.ReceivedDate, P.Client, 
        (SELECT Count(1) 
         FROM Proposals Kt 
         WHERE P.Underwriter = Kt.Underwriter AND Kt.Completed IS NULL 
         GROUP BY Kt.Underwriter) As Kt
    FROM Proposals P
    WHERE P.Completed IS NULL
    ORDER BY Expedite, DueDate, ReceivedDate

MS-Access group by column having count(*) 1 weird behavior?

The GROUP BY is only being applied to the second table. You need to do the UNION ALL first, and then the GROUP BY and HAVING on a SELECT from the combined results.

Not Access specific, but something like this works:

SELECT id FROM
(
  SELECT id FROM a
  UNION ALL 
  SELECT id FROM b
) AS c
GROUP BY id HAVING COUNT(*) > 1

MS Access - in a query, count number of records with specific value in another query

Should review MS documentation on domain aggregate functions. Enclose object references in quote marks and concatenate dynamic parameter.

Instances: DCount("*","[QueryA]","[UniqueID]=" & [UniqueID])

Or build an aggregate query that does the QueryA count by UniqueID and join that query to QueryB.

Access SQL count number of people group by week number

You are lucky I have to change my mind for 15 minutes.

---

Step 1 : Create Calendar table

Create a new table named Calendar with the following fields

• id : autonumber
• Cal_Year : number
• Cal_Week : number

In a module, add the following code and execute it (F5) :

Private Sub Create_Calendar_table()

    Dim Y As Integer
    Dim W As Integer

    For Y = 2006 To 2016
        For W = 1 To 52
            DoCmd.RunSQL "INSERT INTO Calendar (cal_year, cal_week) VALUES (" & Y & "," & W & ")"
        Next W
    Next Y

End Sub

Calendar table is now ready to use :

ID  Cal_year    Cal_week
1   2006        1
2   2006        2
3   2006        3
4   2006        4
5   2006        5
    and so on...

---

Step 2 : Create the query

Note that I am in Europe so my dates are DD/MM. This won't affect your results.

I decompose so you understand the process.

First we need to create a date from the year/week in the calendar table, this can be achieved like this

SELECT Cal_year, Cal_week, DateAdd("ww",Cal_week,DateSerial(Cal_year,1,1)) AS thedate
FROM Calendar

Cal_year    Cal_week    thedate
2006            1       8/01/2006
2006            2       15/01/2006
2006            3       22/01/2006
2006            4       29/01/2006
2006            5       5/02/2006   
and so on...

Next, since we will work on ranges of dates, it is important a to attribute the current date when the people's exit_date is NULL, like this :

nz(date_of_exit, Now)

The field is prepared.

Then, the trick.

We need to JOIN our calendar table with the people table in manner that will return a record for every week on which a person is present.

The key to achieve this is the ON...BETWEEN...AND

SELECT C.Cal_year, C.Cal_Week, P.pname, P.psurname, P.date_of_entry, nz(P.date_of_exit, Now) AS exit_date
FROM [Calendar] C
INNER JOIN ( SELECT [Name] AS pname, [surname] as psurname, date_of_entry, date_of_exit
            FROM people
           ) P  
ON (DateAdd("ww",C.Cal_week,DateSerial(C.Cal_year,1,1)) BETWEEN P.date_of_entry  AND nz(P.date_of_exit, Now))
ORDER BY  C.Cal_year, C.Cal_Week             

Cal_year    Cal_Week pname      psurname    date_of_entry       exit_date
2006            1   foo         bar         1/01/2006               8/01/2006
2010            1   foo1        bar1        5/01/2010               22/04/2016 13:04:39
2010            2   foo1        bar1        5/01/2010               22/04/2016 13:04:39
2010            3   foo1        bar1        5/01/2010               22/04/2016 13:04:39
2010            4   foo1        bar1        5/01/2010               22/04/2016 13:04:39

Note that if you need ALL WEEKS since 2006, even those for which nobody is present, just change the INNER JOIN with a LEFT JOIN

And finally, we exploit the previous query to count the presences by doing a GROUP BY on year and week of the calendar table, and we specify the year 2015 in the WHERE clause otherwise it will count everything since 2006. Which implies that it is very easy to count the presences for any year.

SELECT yyyy, ww , count(*) AS cnt
FROM
(         
    SELECT C.Cal_year AS yyyy, C.Cal_Week AS ww
    FROM [Calendar] C
    INNER JOIN ( SELECT [Name] AS pname, [surname] as psurname, 
                       date_of_entry,  
                       date_of_exit
                FROM people
               ) P  ON (DateAdd("ww",C.Cal_week,DateSerial(C.Cal_year,1,1)) BETWEEN P.date_of_entry  AND nz(P.date_of_exit, Now))
)
WHERE yyyy=2015
GROUP BY yyyy, ww
ORDER BY yyyy, ww

yyyy    ww  cnt
2015    1   1
2015    2   1
2015    3   1
2015    4   1
2015    5   1
2015    6   1
2015    7   1
2015    8   1
2015    9   2
2015    10  2
2015    11  2
2015    12  2
2015    13  2
2015    14  1
2015    15  1
2015    16  1   

---

Well, it took me 40 minutes finally...

Access top n in group

This should work for you. If it doesn't satisfy your requirements, post back what you need.
Your original desire was to have 25, so you'd simply modify the last clause to be HAVING COUNT(*) <= 25

SELECT  a.item, 
        a.category, 
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, 
          a.item, 
          a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.category, COUNT(*) DESC

If you wanted to select more columns from the table, simply add them to the SELECT and `GROUP BY' clauses.

Only when you want to expand the "TOP n for each Category, foo, bar", then you would add those columns to the INNER JOIN clause as well.

--show the top 2 items for each category and year.
SELECT  a.item, 
        a.category, 
        a.year,
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.year = b.year) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, a.item, a.year, a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.year, a.category, COUNT(*) DESC

---

Related Topics