CODEWITHSUNDEEP
sqlms-accesscountdistinct
tmolloy8
tmolloy8

Reputation: 41

Access SQL Rolling 12 months Distinct Count

I have two tables in MS Access, one Dates with just a list of months and years, eg:

MonthNumber YearNumber 1 2013 2 2013 3 2013

The second table is Bedfile which lists the clients namecode, the date, month, and year of their stay, eg:

Namecode Date Month Year Bob 1/1/2013 1 2013 Joe 1/1/2013 1 2013 Bob 1/2/2013 1 2013 Joe 1/2/2013 1 2013 Eric 1/2/2013 1 2013 Bob 2/15/2013 2 2013 Joe 2/16/2013 2 2013 Bob 2/1/2014 2 2014 Joe 2/1/2014 2 2014

What I'm looking for

I'm currently trying to make a query that counts the distinct namecodes for the twelve months prior, eg:

MonthResults YearResults CountResults 1 2013 3 2 2013 3 2 2014 2

This will be a rolling query, meaning that the results column will display the count of the namecodes found within the 12 months prior.

My first SQL attempt is:

SELECT dates.monthnumber, dates.yearnumber, ( SELECT Count(a.namecode) FROM (SELECT DISTINCT bedfile.namecode FROM BEDFILE WHERE bedfile.date BETWEEN dateserial(dates.yearnumber-1, dates.monthnumber, datepart("d", bedfile.date)) AND dateserial(dates.yearnumber, dates.monthnumber, datepart("d", bedfile.date)) ) as a ) FROM dates GROUP BY dates.yearnumber, dates.monthnumber

When I run this, it asks me what the dates.yearnumber and dates.monthnumer are. If I input numbers (say, 2013 and 1 respectively) it gives me the distinct count for only that month.

My second attempt is this:

SELECT Count(a.namecode), dates.monthnumber, dates.yearnumber FROM (SELECT DISTINCT bedfile.namecode, bedfile.month, bedfile.year FROM BEDFILE WHERE bedfile.date BETWEEN dateadd("yyyy", -1, bedfile.date) and bedfile.date ) AS a INNER JOIN dates ON (a.month=dates.monthnumber) AND (a.year=dates.yearnumber) GROUP BY dates.yearnumber, dates.monthnumber;

This second query gives me the distinct counts, but only for each month, rather than the sum of the previous 12.

Does anyone know how to help me create what I'm looking for? Is it even possible in an Access SQL query?

Upvotes: 0

Views: 2138

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

SELECT
    first_date,
    second_date,
    COUNT(name) as num_distinct,
    name
FROM(
    SELECT 
        DISTINCT b.namecode AS name,
        b.date AS first_date,
        DateAdd("yyyy",-1,[b.date]) AS second_date
    FROM bedfile b
    WHERE b.date BETWEEN DateAdd("yyyy",-1,[b.date]) AND b.date
)
GROUP BY name

not sure if this is correct since i've never used MS Access SQL but it seems like this would work

EDIT:

you said that this query

SELECT Count(a.namecode), dates.monthnumber, dates.yearnumber
FROM 
   (SELECT DISTINCT bedfile.namecode, bedfile.month, bedfile.year 
   FROM BEDFILE 
   WHERE bedfile.date 
   BETWEEN dateadd("yyyy", -1, bedfile.date) and bedfile.date
   )  AS a 
INNER JOIN dates 
ON (a.month=dates.monthnumber) AND (a.year=dates.yearnumber)
GROUP BY dates.yearnumber, dates.monthnumber;

returns the distinct count per month so why not just wrap it with another select that selects the SUM() of your count to get the sum per year? another thing you can do is remove the GROUP BY dates.monthnumber.... because that is going to split the data by month instead of showing a year.

I would recommend just grouping by year as it's less sql to write and easier to read.

Upvotes: 0

Related Questions