Report only latest date from many date fields

  • Thread starter Thread starter SusanV
  • Start date Start date
S

SusanV

Hi all,

I have a half dozen date fields for tracking various stages of a job, and
need to report which records were updated in the last week, and ONLY the
most recent entry for that report.

tblJobStatus - fields included for report:
JobNo (pk)
StartDate
CheckedDate
FinishDate
....(others in between, but this gives you an idea)

So, for instance, if job number 123456 started on 10/24/05, was checked on
10/26/05, and completed on 10/27/05, I ONLY want the data

123456 - Finished 10/27/05

I'm having trouble with the SQL - is anyone able to help?

Thanks in Advance,

Susan
 
Dear Susan:

This would be a typical case for the application of a correlated subquery.

First, look at this:

SELECT JobNo, MAX(SomeDate)
FROM YourTable
GROUP BY JobNo

This query will return the greatest date value of SomeDate from among a
number of rows for JobNo. This can then be used in a subquery to filter to
show only that row:

SELECT JobNo, DateType, SomeDate
FROM YourTable T
WHERE SomeDate = (SELECT SomeDate
FROM YourTable T1
WHERE T1.JobNo = T.JobNo)

Now, this is based on a completely different table structure than you have.
It illustrates the reason behind a rule of database design. Having various
dates as columns within a table is strongly discouraged, and this is one of
the reasons why.

The table I recommend would look like:

JobNo
DateType text(20)
SomeDate datetime

Your sample data would look like this:

123456 Started 10/24/05
123456 Checked 10/26/05
123456 Completed 10/27/05

The best policy would be to restructure your data in this way. However, it
is possible to create this appearance with another query and use that to
perform the task you want:

SELECT JobNo, "Started" AS DateType, StartDate
FROM tblJobStatus
WHERE StartDate IS NOT NULL
UNION
SELECT JobNo, "Checked" AS DateType, CheckedDate
FROM tblJobStatus
WHERE CheckedDate IS NOT NULL
SELECT JobNo, "Finished" AS DateType, FinishDate
FROM tblJobStatus
WHERE FinishDate IS NOT NULL

If you were to save this query and use the query I proposed much earlier,
you could get the results you request.

There is an alternative to create a function that finds the maximum from a
set of dates. Such a function fails to tell you WHICH of the 3 date columns
is represented by that date. This may or may not be important to you.

Finally, it may be entirely possible that more than one event occurs on the
same date. The above method would return multiple rows for a single JobNo
in this case.

Tom Ellison
 
Hi Tom,

If I were to redesign the table, I have a form, frmRepProgress, which has
textboxes for all of these fields for data entry / tracking, and I don't
understand how I would salvage or recreate this form using your table
structure. This form is a project requirement, as ALL dates need to be
visible as well as the empty fields for data entry (don't ask why, the Navy
is weird). I realize this is not fully normalized table design - but it was
set up this way a long time ago and now I have to live with it. Perhaps I'll
look into redesign at some point in the future, but for now at least I'm
stuck with my current table structure.

This is very frustrating!

Thanks for your input, and any suggestion you might have on making that form
usable with a redesign would be welcome - if I can manage the form I can
probably go with the structure change.

SusanV
 
Unfortunately, your data is not in an optimal structure for Access Queries
to perform this task quickly and easily. If the structure were like the
following, it was be easier:

TblJobStatus
JobNo
StatusDate
StatusCode

This way, you could easily get the Max statusdate per JobNo, as described on
this website:
http://www.mvps.org/access/queries/qry0020.htm
 
Dear Susan:

Please note that I did give you a solution without making the table and form
changes. The UNION query will transform the data as required, and you can
then perform the query I gave you based on that.

If you are interested, there is also a simple way to implement the corrected
design on a form, using a subform. You would also probably need to add a
table of what I called DateTypes.

Tom Ellison
 
For any interested, here's the solution I worked up:

1. Delete temp table if it exists
2. Rather than using a UNION, use CREATE TABLE, making RepNum the PK
3. Working BACKWARD from finishDate toward StartDate, APPEND to the temp
Table I created in step one (PK definition ensures no duplicate RepNums)


''''''''''''''''Code start

Dim sql As String

If Not IsNull(DLookup("Name", "MSysObjects", "[Name] = 'tblWkly'")) Then
DoCmd.DeleteObject acTable, "tblWkly"
End If
sql = "CREATE TABLE tblWkly ( " _
& "RepNum TEXT (20) CONSTRAINT PK_tblWkly PRIMARY KEY, " _
& "fldModType TEXT (20), " _
& "fldModDate DATE)"
DoCmd.RunSQL sql

sql = "INSERT INTO tblWkly ( RepNum, fldModType, fldModDate ) " _
& "SELECT tblPMO.RepNum, 'MMLReg' AS fldModType, tblPMO.MMLReg AS
fldModDate " _
& "FROM tblPMO " _
& "WHERE (((tblPMO.MMLReg) Between Date() And (Date()-7)));"
DoCmd.RunSQL sql
sql = "INSERT INTO tblWkly ( RepNum, fldModType, fldModDate ) " _
& "SELECT tblPMO.RepNum, 'MMLPlan' AS fldModType, tblPMO.MMLPlan AS
fldModDate " _
& "FROM tblPMO " _
& "WHERE (((tblPMO.MMLPlan) Between Date() And (Date()-7)));"
DoCmd.RunSQL sql
sql = "INSERT INTO tblWkly ( RepNum, fldModType, fldModDate ) " _
& "SELECT tblPMO.RepNum, 'MMLFBR' AS fldModType, tblPMO.MMLFBR AS
fldModDate " _
& "FROM tblPMO " _
& "WHERE (((tblPMO.MMLFBR) Between Date() And (Date()-7)));"
DoCmd.RunSQL sql
''''''Repeat moving backwards through timeline fields to Start

Hopefully someone can benefit from this

;-D


SusanV
 
Back
Top