I’m referring to MS Access SQL. Suppose I have a column with dates of birth of a population and the decades which these dates fall into.
Year Decade 1971 8 1953 6 1958 6 1929 3 1930 4
I want to create a query which will show how many people were born in each decade of a century.
I know it’s going to be something like
SELECT (Year mod 100) 10 + 1 as [Decade], Count(*) as [How many people] FROM People GROUP BY (Year mod 100) 10 + 1
My problem is that there might be some decades in which no one was born from my population and I still want these to show up in my query, with a zero.
My ideal solution would be defining a table on the fly, consisting of rows {1,2,3,4…}, very much like you’d do in any programming language, say in Python decades = range(1,10), then creating the table with the counted people, and then joining these two together with a left join.
It seems not possible, but I’m a newbie to SQL and databases. Is that possible? What are other approaches?
Advertisement
Answer
MsAccess does not have a function like Range() that you can use. What I have done in my databases is create a table of numbers to use for cases like this.
The simplest way to create this table is by using an Excel spreadsheet to build the column of numbers (for instance, from 1 to 1,000) and then import the spreadsheet as a new table. Then make whatever adjustments are appropriate – for example, the new table should have a primary key on the numbers column, and the numbers column should probably be of a long integer data type. You could call the table [Numbers] and name the column [NumberValue] – these names are up (you could just as easily call your column [Nums] or even just [N]). But I would caution against using the name [Number] for a table or column because Number is a datatype name and MSAccess does not always play nicely with names that are SQL or VBA keywords.
Now you can use your new table with regular sql: Select * from [Numbers] where NumberValue >= 1 and NumberValue <= 10