Minimum Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
CCross said:
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.
Click to expand...

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
 
Back
Top