If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query the ealiest date within several columns in table
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 |
#2
|
|||
|
|||
Query the ealiest date within several columns in table
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]); "Alison" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|