Query the ealiest date within several columns in table

[Alison]

Hi, I have a database of projects with four different date columns (DOB,
First LWS, First SLDWS, and First SA). Some of these fields are blank.
I want to run a query that will give me the earliest date from these four
columns, but I don't know how.
Can anyone help me?
Very much appreciated!
Alison

 

[KARL DEWEY]

Try these queries --
qryDateUnion_1 --
SELECT ProjectID, "DOB" AS [Field1], DOB AS [Field2]
FROM YourTable
UNION ALL SELECT ProjectID, "First LWS" AS [Field1], [First LWS] AS [Field2]
FROM YourTable
UNION ALL SELECT ProjectID, "First SLDWS" AS [Field1], [First SLDWS] AS
[Field2]
FROM YourTable
UNION ALL SELECT ProjectID, "First SA" AS [Field1], [First SA] AS [Field2]
FROM YourTable;

qryDateUnion_2 --
SELECT ProjectID, Min([Field2]) AS [Earliest date]
FROM qryDateUnion_1
GROUP BY ProjectID

SELECT qryDateUnion_1.ProjectID, qryDateUnion_1.[Field1] AS [Field name],
[Earliest date]
FROM qryDateUnion_1 INNER JOIN qryDateUnion_2 ON (qryDateUnion_1.ProjectID
=qryDateUnion_2.ProjectID) AND (qryDateUnion_1.[Field2] =
qryDateUnion_2.[Earliest date]);