Minimum Date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CCross - 29 Sep 2005 21:28 GMT

I would like to find the minimum date by comparing the StartDate field in 9
separate department tables. The 9 department tables are linked to a main
table WBS by WBS#. I would like the WBSStartDate to be the minimum
StartDate. I am unsure if I can use DLookup or just a nested IIf statement.
Any help greatly appreciated.
CCross

Reply to this Message

KARL DEWEY - 29 Sep 2005 21:38 GMT

Seems like bad DB structure to have 9 department tables instead of one with a
department field.

For a one time check why not append all into a temp file and check?

> I would like to find the minimum date by comparing the StartDate field in 9
> separate department tables. The 9 department tables are linked to a main
> table WBS by WBS#. I would like the WBSStartDate to be the minimum
> StartDate. I am unsure if I can use DLookup or just a nested IIf statement.
> Any help greatly appreciated.
> CCross

Reply to this Message

John W. Vinson/MVP - 30 Sep 2005 05:56 GMT

>I would like to find the minimum date by comparing the StartDate field in 9
> separate department tables. The 9 department tables are linked to a main
> table WBS by WBS#. I would like the WBSStartDate to be the minimum
> StartDate. I am unsure if I can use DLookup or just a nested IIf
> statement.

Karl's exactly right about the table design. One alternative to the append
to a new table is to use a UNION query:

SELECT Min([WBSStartDate]) As Earliest
FROM
(SELECT WBSStartDate, FieldX, FieldY FROM DepartmentA
UNION ALL
SELECT WBSStartDate, FieldX, FieldY FROM DepartmentB
UNION ALL
SELECT... <etc>)

See the online help for UNION.

John W. Vinson/MVP

Reply to this Message