How to include empty rows in Access query results

Question

I have an Access 2010 database with client information. I need to create a table of the number of clients in each age. The agency I am reporting to wants a report with the number of clients of every age from 0 - 100 years listed. The SQL query below will create the required report, but does not include ages with zero clients.

SELECT AgeNum & " years" AS [Age], Count(*) AS [Count]
FROM (SELECT Int(DateDiff("d", Clients.dob, now())/365.25) AS AgeNum 
FROM Clients)  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].AgeNum;

How can I have the query return the empty rows with 0 in the Count column?

I looked around and found this:

How can I create a row for values that don't exist and fill the count with 0 values?

They create a table of values to lookup the empty groups. It is very similar to what I need except it uses a Coalesce function which is not supported in Access 2010.

Answer 1

You can use instead of COALESCE Nz function:

Nz([Age],0)

And yes, your link should work for you.

Answer 2

Create a 'sequence' table of all possible integers (FYI in UK medical data dictionaries we use 220 as the maximum age in years), then 'anti-join' to this table. You could use a view for your original results.

The following SQL DDL requires ANSI-92 Query Mode (probably better for the SQL Server coder than the default Query Mode) but can also be created maually using the Access GUI tools:

CREATE TABLE Seq ( seq INT NOT NULL UNIQUE );


INSERT INTO Seq VALUES ( 1 );
INSERT INTO Seq VALUES ( 2 );
INSERT INTO Seq VALUES ( 3 );
...
(you can use Excel to create this script!)
...
INSERT INTO Seq VALUES ( 100 );


CREATE VIEW ClientAgeTallies ( AgeInYears, Tally )
AS
SELECT dt.AgeInYears, COUNT(*) AS Tally
  FROM ( SELECT INT(DATEDIFF( 'd', c.dob, NOW() ) / 365.25) AS AgeInYears
           FROM Clients AS c ) AS dt
 GROUP 
    BY dt.AgeInYears;


SELECT AgeInYears, Tally
  FROM ClientAgeTallies
UNION
SELECT seq AS AgeInYears, 0 AS Tally
  FROM Seq
 WHERE seq NOT IN ( SELECT AgeInYears FROM ClientAgeTallies );

Answer 3

The system only knows there is an age if a particular record exists. If you want to have a list of age between 1..100, you need to tell or provide the system that you are looking for 0..100 ages. By providing a list of ages you are looking for, system will automatically return 0/null if the requested age is not found withing your searched records.

As others mentioned, you can have a table with 1..100 as rows and compare them in your SQL or you could generate list of numbers with SQL.

Some DBMS provide a default table called dual which has one column and one row, you can use that table for any queries that does not have a from table. In your access application, create a table called "dual" and insert one row.

Now execute this query:

SELECT TMain.counter
FROM (SELECT (T2.mAge*t3.mFactor10)+t1.mAge AS counter
FROM (select 1 as mAge from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T1, 
(select 0 as mAge from dual
union all select 1 from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T2,
(select 10 as mFactor10 from dual) AS T3 )  AS TMain
WHERE (((TMain.counter) Between 1 And 100));

this will produce 100 rows from 1..100.

you can then use this result as outer table for your SQL and find/count anyone whose age is on this list. the logic would be:

select all age 
from the reqeusted age list
find  and count/return all matched records or return 0 if no records found.

In SQL, it would be something like this,

SELECT TMain.counter as Age,
(SELECT Count(*) AS [Count]
FROM (SELECT Int(DateDiff("d", Clients.dob, now())/365.25) AS AgeNum 
FROM Clients)  AS [%$##@_Alias]
WHERE (TMain.counter = [%$##@_Alias].ageNum)
GROUP BY [%$##@_Alias].AgeNum) as number_of_clients
FROM (SELECT (T2.mAge*t3.mFactor10)+t1.mAge AS counter
FROM (select 1 as mAge from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T1, 
(select 0 as mAge from dual
union all select 1 from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T2,
(select 10 as mFactor10 from dual) AS T3 )  AS TMain
WHERE (((TMain.counter) Between 1 And 100));

this will produce: age from 1..100 as well as number of clients for each age and null for null/empty no results . of course, you can dynamically extend or shorten the age list.