Databases
StackOverflow2013
Postsdbo
POMS Access SQL - Capture changes in status over time
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMS Access SQL - Capture changes in status over time
    primarykey
    data
    text
    <p>I have an Access 2007 database that tracks document progression through time. The progression goes like:</p> <ol> <li>Created</li> <li>Sent for Review</li> <li>Reviewed</li> <li>Sent for Approval</li> <li>Approved</li> </ol> <p>I've created a history table for changes in document status with columns like this:</p> <pre><code>hist_id doc_id month step status datestamp </code></pre> <p>I've created a query that returns statuses for the end of the month, like so:</p> <pre><code>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; </code></pre> <p>to get....</p> <pre><code>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 </code></pre> <p>But what I really need is my query to also return documents in months for which the status hasn't changed. For example, document <code>a</code>'s status became <code>reviewed</code> on <code>19/02/2011</code>, but that's the last time it shows up in the results above. It should actually show up in every month afterward as <code>reviewed</code> until it later becomes <code>sent for approval</code>.</p> <p>So I'm trying to modify my query (or query the above query) to provide results like below...</p> <pre><code> 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 </code></pre> <p>Thanks for your help... I really don't even know where to start here.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USTommy O'Dell
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMS Access SQL - Capture changes in status over time
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMS Access SQL - Capture changes in status over time
      1. USpedram
      1. VTFavorite
    3. VO
      singulars
      1. POMS Access SQL - Capture changes in status over time
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload