How to Compare 4 date fields?

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

Guest

Hi,
I have a table with 4 fields call InterviewDate1,
InterviewDate2,InterviewDate3,InterviewDate4.
How can I compare which InterviewDate is enter lastest?
Thank you.
MN
 
Best way is to fix your table structure. You should have a table with A
field for the InterviewDate and some other fields to tie that field to the
record in your current table.

Next option is to use a UNION query to normalize your data.
SELECT FieldsThatIDRecord, InterviewDate1 as InterviewDate
FROM YourTable
UNION
SELECT FieldsThatIDRecord, InterviewDate2
FROM YourTable
UNION
SELECT FieldsThatIDRecord, InterviewDate3
FROM YourTable
UNION
SELECT FieldsThatIDRecord, InterviewDate4
FROM YourTable

Then you can use the Max function against the UNION (or the revised
structure) to get the dates you want.

SELECT FieldsThatIDRecord, Max(InterviewDate) as LatestDate
FROM TheUnionQuery
GROUP BY FieldIdRecords

Final option is to use some vba code. Here is a function that I built
before I got smarter. It was built for numbers, but since a date is stored
as a number in Access, you should be ok

SELECT <List your fields>,
CDate(fGetMaxNumber(InterviewDate1,InterviewDate2,InterviewDate3,InterviewDate4))
as LatestDate
FROM YourTable
WHERE ...


'------------- Code Starts --------------
Public Function fGetMaxNumber(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call: myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0)
returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

vMax = -1E+308 'very large negative number
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then
dblCompare = CDbl(Values(i))
If dblCompare > vMax Then
vMax = dblCompare
tfFound = True
End If
End If
Next

If tfFound Then
fGetMaxNumber = vMax
Else
fGetMaxNumber = Null
End If

End Function
 
My suggestion, if it works for you, is to combine John Spencer's options
(1) and (2): Create a new version of the Table using the Union Query.

John said:
Best way is to fix your table structure. You should have a table with A
field for the InterviewDate and some other fields to tie that field to the
record in your current table.

Next option is to use a UNION query to normalize your data.
SELECT FieldsThatIDRecord, InterviewDate1 as InterviewDate
FROM YourTable
UNION
SELECT FieldsThatIDRecord, InterviewDate2
FROM YourTable
UNION
SELECT FieldsThatIDRecord, InterviewDate3
FROM YourTable
UNION
SELECT FieldsThatIDRecord, InterviewDate4
FROM YourTable
Click to expand...

For example, suppose your Table looks like this:

YourTableID Interview Interview Interview Interview
Date1 Date2 Date3 Date4
----------- --------- --------- --------- ---------
-1686977725 1/2/2005 2/3/2005 3/4/2005 4/5/2005
811238600 8/9/2005 6/7/2005 5/6/2005

Your Union Query could look like this:

[Q_MakeDateTableUnion]

SELECT YourTable.YourTableID AS FieldsThatIDRecord,
YourTable.InterviewDate1 as InterviewDate, 1 AS Type
FROM YourTable
UNION SELECT YourTableID, InterviewDate2, 2
FROM YourTable
UNION SELECT YourTableID, InterviewDate3, 3
FROM YourTable
UNION SELECT YourTableID, InterviewDate4, 4
FROM YourTable
ORDER BY InterviewDate;

.... then (option 1) you could use a Make-Table Query to write all this
to a new Table, [DateTable]:

SELECT * INTO DateTable
FROM Q_MakeDateTableUnion AS QU
WHERE (((QU.InterviewDate) Is Not Null))
ORDER BY QU.InterviewDate;

This Query, when run (but you should BACK UP your database file first),
produces this new Table:

[DateTable]

FieldsThatIDRecord InterviewDate Type
------------------ ------------- ----
-1686977725 1/2/2005 1
-1686977725 2/3/2005 2
-1686977725 3/4/2005 3
-1686977725 4/5/2005 4
811238600 5/6/2005 4
811238600 6/7/2005 3
811238600 8/9/2005 1

With the addition of the [Type] field, you can determine which of the
four [InterviewDate...] values a particular date came from, if that
might be important.
Then you can use the Max function against the UNION (or the revised
structure) to get the dates you want.

SELECT FieldsThatIDRecord, Max(InterviewDate) as LatestDate
FROM TheUnionQuery
GROUP BY FieldIdRecords
Click to expand...

I changed this SQL slightly:

SELECT [FieldsThatIDRecord],
Max([InterviewDate]) AS LatestDate
FROM DateTable
GROUP BY [FieldsThatIDRecord];

.... getting this result:

FieldsThatIDRecord LatestDate
------------------ ----------
-1686977725 4/5/2005
811238600 8/9/2005

Incidentally, if there's some reason to keep the original format, you
don't need to do that in a Table. The following Query:

SELECT Q0.FieldsThatIDRecord,
Q1.InterviewDate AS InterviewDate1,
Q2.InterviewDate AS InterviewDate2,
Q3.InterviewDate AS InterviewDate3,
Q4.InterviewDate AS InterviewDate4
FROM (((
[SELECT DISTINCT DateTable.FieldsThatIDRecord
FROM DateTable]. AS Q0 LEFT JOIN
[SELECT FieldsThatIDRecord, InterviewDate
FROM DateTable WHERE (((Type)=1))]. AS Q1
ON Q0.FieldsThatIDRecord = Q1.FieldsThatIDRecord) LEFT JOIN
[SELECT FieldsThatIDRecord, InterviewDate
FROM DateTable WHERE (((Type)=2))]. AS Q2
ON Q0.FieldsThatIDRecord = Q2.FieldsThatIDRecord) LEFT JOIN
[SELECT FieldsThatIDRecord, InterviewDate
FROM DateTable WHERE (((Type)=3))]. AS Q3
ON Q0.FieldsThatIDRecord = Q3.FieldsThatIDRecord) LEFT JOIN
[SELECT FieldsThatIDRecord, InterviewDate
FROM DateTable WHERE (((Type)=4))]. AS Q4
ON Q0.FieldsThatIDRecord = Q4.FieldsThatIDRecord
ORDER BY Q0.FieldsThatIDRecord;

.... reconstructs the original format, but without requiring you to
maintain a Table containing a hard-to-manage repeating group (the 4 dates):

FieldsThatIDRecord Interview Interview Interview Interview
Date1 Date2 Date3 Date4
------------------ --------- --------- --------- ---------
-1686977725 1/2/2005 2/3/2005 3/4/2005 4/5/2005
811238600 8/9/2005 6/7/2005 5/6/2005


[...]
 
Back
Top