MS Access SQL - Capture changes in status over time

Question

I have an Access 2007 database that tracks document progression through time. The progression goes like:

1. Created
2. Sent for Review
3. Reviewed
4. Sent for Approval
5. Approved

I've created a history table for changes in document status with columns like this:

hist_id    doc_id    month   step    status  datestamp

I've created a query that returns statuses for the end of the month, like so:

SELECT doc_id, month, step, status, datestamp
FROM hist
WHERE (((hist.datestamp) In 
(
      Select Top 1 h.[datestamp]
      From hist as h
      Where h.[doc_id] = hist.[doc_id] and h.[month] = hist.[month]
      Order By h.[datestamp] DESC))
)
ORDER BY month, doc_id DESC;

to get....

doc_id  month   step status             datestamp
a       2011-01 2    sent for review    18/01/2011
b       2011-02 1    created            01/02/2011
a       2011-02 3    reviewed           19/02/2011
c       2011-03 1    created            07/03/2011
d       2011-03 1    created            08/03/2011
e       2011-06 1    created            14/06/2011
f       2011-07 1    created            05/07/2011
g       2011-07 4    sent for approval  18/07/2011
h       2011-07 2    sent for review    14/07/2011
f       2011-08 3    reviewed           29/08/2011
g       2011-08 5    approved           17/08/2011
h       2011-08 1    created            10/08/2011
e       2011-09 3    reviewed           17/09/2011

But what I really need is my query to also return documents in months for which the status hasn't changed. For example, document a's status became reviewed on 19/02/2011, but that's the last time it shows up in the results above. It should actually show up in every month afterward as reviewed until it later becomes sent for approval.

So I'm trying to modify my query (or query the above query) to provide results like below...

 doc_id month   step    status          datestamp
a   2011-01 2   sent for review     18/01/2011
a   2011-02 3   reviewed        19/02/2011
b   2011-02 1   created         01/02/2011
a   2011-03 3   reviewed        19/02/2011
b   2011-03 1   created         01/02/2011
c   2011-03 1   created         07/03/2011
d   2011-03 1   created         08/03/2011
a   2011-04 3   reviewed        19/02/2011
b   2011-04 1   created         01/02/2011
c   2011-04 1   created         07/03/2011
d   2011-04 1   created         08/03/2011
a   2011-05 3   reviewed        19/02/2011
b   2011-05 1   created         01/02/2011
c   2011-05 1   created         07/03/2011
d   2011-05 1   created         08/03/2011
a   2011-06 3   reviewed        19/02/2011
b   2011-06 1   created         01/02/2011
c   2011-06 1   created         07/03/2011
d   2011-06 1   created         08/03/2011
e   2011-06 1   created         14/06/2011
a   2011-07 3   reviewed        19/02/2011
b   2011-07 1   created         01/02/2011
c   2011-07 1   created         07/03/2011
d   2011-07 1   created         08/03/2011
e   2011-07 1   created         14/06/2011
f   2011-07 1   created         05/07/2011
g   2011-07 4   sent for appr   18/07/2011
h   2011-07 2   sent for rev    14/07/2011
a   2011-08 3   reviewed        19/02/2011
b   2011-08 1   created         01/02/2011
c   2011-08 1   created         07/03/2011
d   2011-08 1   created         08/03/2011
e   2011-08 1   created         14/06/2011
f   2011-08 3   reviewed        29/08/2011
g   2011-08 5   approved        17/08/2011
h   2011-08 1   created         10/08/2011
a   2011-09 3   reviewed        19/02/2011
b   2011-09 1   created         01/02/2011
c   2011-09 1   created         07/03/2011
d   2011-09 1   created         08/03/2011
e   2011-09 1   reviewed        17/09/2011
f   2011-09 3   reviewed        29/08/2011
g   2011-09 5   approved        17/08/2011
h   2011-09 1   created         10/08/2011

Thanks for your help... I really don't even know where to start here.

Answer 1

This works in SQL and doesn't use any special features, so should work in MS-ACCESS SQL as well.

First, you will need to create a table on months.

create table monthly (monthN char(7))

insert into monthly values('2011-01')
insert into monthly values('2011-02')
...

and populate it with all the months you need.

With that table built, the following query should return what you are looking for:

select d1.doc_id,d1.monthN,d1.step,d1.status,d1.dateStamp
from monthly m1
join docs d1 on d1.monthN=m1.monthN
union
select d2.doc_id,zz.monthN,d2.step,d2.status,d2.dateStamp
from docs d2
join
(
    select aa.doc_id,aa.monthN,bb.EndM from
    (
    select yy.doc_id,yy.monthN from
    (   
        select d3.doc_id,m2.monthN
        from monthly m2
        join (select distinct doc_id from docs) d3 on 1=1
        ) yy
    left join docs xx on xx.doc_id=yy.doc_id and xx.monthN=yy.MonthN
    where xx.hist_id is null
    ) aa
    join (select doc_id,MIN(monthN) as startM,MAX(monthN) as EndM 
          from docs group by doc_id) 
          bb on bb.doc_id=aa.doc_id and aa.monthN>=bb.StartM
) zz
on zz.doc_id=d2.doc_id and zz.EndM=d2.monthN
order by d1.monthN,d1.doc_id 

I would suggest running each inner query separately to help follow what is being done...

Answer 2

Thanks to some clues from Sparky's answer, I was able to piece together something that works for me.

Steps

1. Create a months table to contain list of months like 2011-08

2. Create a month_range query to get the range of months from actual documents

   SELECT month_no FROM months WHERE month BETWEEN (minium month for hist) AND (max month for hist)

3. Do a cross product query with month_range and hist tables, where hist.month_no <= month_range.month

4. The step above leaves a table with multiple status changes per month per document. Just GROUP BY on month_no and doc_no with max(hist_id) in the SELECT

5. INNER JOIN the result from above with the hist table using hist_id to get access to the status

The exact final query that I used looks like this....

SELECT xx.month_no, xx.swp, xx.hist_id, h.status

FROM 
(
     SELECT zz.month_no, zz.swp, Max(zz.hist_id) AS hist_id
     FROM 
     (
          SELECT * FROM month_range AS mr, hist AS h 
          WHERE h.[first_of_month] <= mr.[first_of_month]
     ) zz
     GROUP BY zz.month_no, zz.swp

)  xx 

INNER JOIN hist as h ON xx.hist_id = h.hist_id;