Hi All,

A bit of background, i have inherited an Access 2007 database and the person who created it has now left the company unfortunately. I've been asked to make some updates with which i am struggling. I'll try make my explanation as simple as possible:

The database is used to track document progress and each document can have multiple statuses (In Prep, Review, Approval, Released, Under Revision, Withdrawn). These are stored in a table (ORD_Status) with an Autonumber as an ID. I then have another table (ORD_History) which stores further information about each document (ID, Title, Status, Status Date and Owner). I need to create a query which returns the Title and the lastest status date with associated status description. I have no problem with the date query and can return each document Title with the last date stored, but as soon as i drop status into the query i get a status and date for each stage. If i use 'last' from totals i will get the last status returned, however if a document goes back say from Released (ID 4 in ORD_Status table) to Review (ID 2 in ORD_Status table) then this is not updated in the output. I have tried using MAX also and have the same issue. I have tried many ways but each time i get stuck with this Status issue. Is there any way i can run my select query which will take the Title, Last Date and the Status associated with that date?

Heres an example of the tables i have:

ORD_Status
ID Description
1 In Preparation
2 Review
3 Approval
4 Released
5 Under Revision
6 Withdrawn

ORD_History
000038 In Preparation 13/05/2010
000038 Released 13/05/2010
000038 Under Revision 13/07/2010
000038 Released 10/08/2010
000038 Released 03/11/2011
000002 In Preparation 18/09/2008
000002 Approval 22/09/2008
000002 Released 24/09/2008
000002 Released 19/10/2011

Any help would be most appreciated as at present im going round in circles.

Regards
Donna

This post has been edited by SoTrue: Apr 17 2012, 04:40 AM

There are a couple of standard ways to do this; this is one...

Select T.* from Ord_history as T
Inner Join
(Select ID, Max(Status_date) as Mdate from Ord_history) as qry1
On T.ID= qry1.ID and T.Status_date = qry1.Mdate

Check table names and field names are correct.

Thanks Peter, I've got it all working now. This is a great forum... (IMG:style_emoticons/default/notworthy.gif)

I have come across another problem now, which maybe someone can help me with.

I have a subform, within an overall form used to create a new document. This subform was initially a way of creating a new work package which underlies the overall document. Since modifying it to show the status of eack work package under each document (Using calculated fields) i cannot add a new record. Before adding the status to each work package in the form, it was possible for a new line to be added (Record) whereby the work package ID was an Autonumber from tbl_WorkPackage, the title was free text and a drop down box was available to select which department the work package belongs to. After this a button would be clicked which would open the work package creation form and auto populate the parent document (ORD Reference) using a Dlookup (unfortunately, the guy that built the database left before i got involved hence why i am struggling). I have attached some images of the forms to make it clearer.

Image 1: Original form and subform, before adding work package status.
Image 2: New form and subform with status added (Calculated fields).
Image 3: Related record to populate the remaining information for the Work Package.

I hope i've explained things properly.

You need to post a new question.

No-one will see it otherwise.