Make table from a query

[fd1]

Hi All,
I have a query that outputs a "ver_number" unique field and five different date fields (date1, date2, ...date5) that can have null values.
I would like to make a table out of this query that takes the "ver_number" field records and assigns for each "ver_number" a date called "final_date" selected from the five dates in this order:
final_date=date1 if null then
final_date=date2 if null then
final_date=date3 and so on...
if all five dates are null then this ver_number is skipped and not added to the table.

[mlcampeau]

Hi All,
I have a query that outputs a "ver_number" unique field and five different date fields (date1, date2, ...date5) that can have null values.
I would like to make a table out of this query that takes the "ver_number" field records and assigns for each "ver_number" a date called "final_date" selected from the five dates in this order:
final_date=date1 if null then
final_date=date2 if null then
final_date=date3 and so on...
if all five dates are null then this ver_number is skipped and not added to the table.

Try something like this....

SELECT [Ver_number], IIf([date1] Is Not Null,[date1],IIf([date2] Is Not Null,[date2],IIf([date3] Is Not Null,[date3],IIf([date4] Is Not Null,[date4],IIf([date5] Is Not Null,[date5],""))))) AS Final_Date INTO NewTableName
FROM yourTableName
WHERE (((IIf([date2] Is Not Null,[date2],IIf([date3] Is Not Null,[date3],IIf([date4] Is Not Null,[date4],IIf([date5] Is Not Null,[date5],""))))) Is Not Null));

[ADezii]

Hi All,
I have a query that outputs a "ver_number" unique field and five different date fields (date1, date2, ...date5) that can have null values.
I would like to make a table out of this query that takes the "ver_number" field records and assigns for each "ver_number" a date called "final_date" selected from the five dates in this order:
final_date=date1 if null then
final_date=date2 if null then
final_date=date3 and so on...
if all five dates are null then this ver_number is skipped and not added to the table.

SELECT Ver_Number, Date1, Date2, Date3, Date4, Date5, fCalculateFinalDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS Final_Date INTO tblNewFinalDate
FROM tblFinalDate;

Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
If IsNull(Date_1) Then
  If IsNull(Date_2) Then
    If IsNull(Date_3) Then
      If IsNull(Date_4) Then
        If IsNull(Date_5) Then
          fCalculateFinalDate = Null
        End If
      Else
        fCalculateFinalDate = Date_4
      End If
    Else
      fCalculateFinalDate = Date_3
    End If
  Else
    fCalculateFinalDate = Date_2
  End If
Else
  fCalculateFinalDate = Date_1
End If
End Function

[mlcampeau]

SELECT Ver_Number, Date1, Date2, Date3, Date4, Date5, fCalculateFinalDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS Final_Date INTO tblNewFinalDate
FROM tblFinalDate;

Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
If IsNull(Date_1) Then
  If IsNull(Date_2) Then
    If IsNull(Date_3) Then
      If IsNull(Date_4) Then
        If IsNull(Date_5) Then
          fCalculateFinalDate = Null
        End If
      Else
        fCalculateFinalDate = Date_4
      End If
    Else
      fCalculateFinalDate = Date_3
    End If
  Else
    fCalculateFinalDate = Date_2
  End If
Else
  fCalculateFinalDate = Date_1
End If
End Function

Just out of curiousity ADezii, do you know if your method is more efficient than what I suggested? (I've never actually used a Make Table query until I tested my sql posted above) It just seems like so much more work to get the same result, but if it reduces bloat and is faster, then I could see the point.

[fd1]

Try something like this....

SELECT [Ver_number], IIf([date1] Is Not Null,[date1],IIf([date2] Is Not Null,[date2],IIf([date3] Is Not Null,[date3],IIf([date4] Is Not Null,[date4],IIf([date5] Is Not Null,[date5],""))))) AS Final_Date INTO NewTableName
FROM yourTableName
WHERE (((IIf([date2] Is Not Null,[date2],IIf([date3] Is Not Null,[date3],IIf([date4] Is Not Null,[date4],IIf([date5] Is Not Null,[date5],""))))) Is Not Null));

Thanks mlcampeau, it did work but one thing it didn't do is ignore the [ver_number] that had all 5 dates null and not add it to the new table.

[fd1]

Thanks ADezii, that worked too
how can I tweak your code to not include the "ver_number" that has all 5 dates null?

[fd1]

ADezii, your code seems to have missed Date5.
All records that have only Date5 as not null are having null Final_Date in the new table

[ADezii]

Thanks ADezii, that worked too
how can I tweak your code to not include the "ver_number" that has all 5 dates null?

INSERT INTO tblNewFinalDate ( Ver_Number, Date1, Date2, Date3, Date4, Date5, Final_Date )
SELECT Ver_Number, Date1, Date2, Date3, Date4, Date5, fCalculateFinalDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS Final_Date
FROM tblFinalDate
WHERE Date1 Is Not Null OR Date2 Is Not Null OR Date3 Is Not Null OR Date4 Is Not Null OR Date5 Is Not Null;

[ADezii]

ADezii, your code seems to have missed Date5.
All records that have only Date5 as not null are having null Final_Date in the new table

Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
If IsNull(Date_1) Then
  If IsNull(Date_2) Then
    If IsNull(Date_3) Then
      If IsNull(Date_4) Then
        If IsNull(Date_5) Then
          fCalculateFinalDate = Null
        Else
          fCalculateFinalDate = Date_5
        End If
      Else
        fCalculateFinalDate = Date_4
      End If
    Else
      fCalculateFinalDate = Date_3
    End If
  Else
    fCalculateFinalDate = Date_2
  End If
Else
  fCalculateFinalDate = Date_1
End If
End Function

[NeoPa]

Late to the party here, but wouldn't Nz() make life a lot easier for the dates?

[mlcampeau]

Thanks mlcampeau, it did work but one thing it didn't do is ignore the [ver_number] that had all 5 dates null and not add it to the new table.

SELECT [Ver_number], IIf([date1] IS NOT NULL,[date1],IIf([date2] IS NOT NULL,[date2],IIf([date3] IS NOT NULL,[date3],IIf([date4] IS NOT NULL,[date4],IIf([date5] IS NOT NULL,[date5],""))))) AS Final_Date INTO NewTableName
FROM Table1
WHERE (((IIf([date2] IS NOT NULL,[date2],IIf([date3] IS NOT NULL,[date3],IIf([date4] IS NOT NULL,[date4],IIf([date5] IS NOT NULL,[date5],Null))))) IS NOT NULL));

This worked in my test database. I changed the "" to Null in the final IIF statement

[fd1]

Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
If IsNull(Date_1) Then
  If IsNull(Date_2) Then
    If IsNull(Date_3) Then
      If IsNull(Date_4) Then
        If IsNull(Date_5) Then
          fCalculateFinalDate = Null
        Else
          fCalculateFinalDate = Date_5
        End If
      Else
        fCalculateFinalDate = Date_4
      End If
    Else
      fCalculateFinalDate = Date_3
    End If
  Else
    fCalculateFinalDate = Date_2
  End If
Else
  fCalculateFinalDate = Date_1
End If
End Function

Thanks ADezii, that seems to work now

[fd1]

SELECT [Ver_number], IIf([date1] IS NOT NULL,[date1],IIf([date2] IS NOT NULL,[date2],IIf([date3] IS NOT NULL,[date3],IIf([date4] IS NOT NULL,[date4],IIf([date5] IS NOT NULL,[date5],""))))) AS Final_Date INTO NewTableName
FROM Table1
WHERE (((IIf([date2] IS NOT NULL,[date2],IIf([date3] IS NOT NULL,[date3],IIf([date4] IS NOT NULL,[date4],IIf([date5] IS NOT NULL,[date5],Null))))) IS NOT NULL));

This worked in my test database. I changed the "" to Null in the final IIF statement

Thanks mlcampeauIt, it worked now after adding the condition for date1 in the WHERE clause which was missing in your code.
I like the fact that I have 2 sets of code to work with, that way I'm able to verify results.

[ADezii]

Thanks ADezii, that seems to work now

Not a problem, glad it's working for you. This is a classic example of more than 1 way to skin an apple. You have 2 diverse approaches which arrive at the same solution via different execution plans. If you are ever working with a large number of Records, you may want to see which approach is faster. My initial guess would be that the Function approach would be significantly faster because of the nature of IIF() being notoriously innefficient, the fact that it evaluates both conditions, and there are simply too many of them in the statement. Do not go by my say so since I am definately prejudiced against certain Functions, notably IIf().

[ADezii]

Late to the party here, but wouldn't Nz() make life a lot easier for the dates?

Welcome to the party, NeoPa! It probably would make life easier, I just have this thing about using the Nz() Function with Dates.

[mlcampeau]

Thanks mlcampeauIt, it worked now after adding the condition for date1 in the WHERE clause which was missing in your code.
I like the fact that I have 2 sets of code to work with, that way I'm able to verify results.

Whoops, didn't notice I missed date1! Glad you got it to work anyhow!

[NeoPa]

Welcome to the party, NeoPa! It probably would make life easier, I just have this thing about using the Nz() Function with Dates.

Oh well, multiple solutions and ideas is always worthwhile anyway, as not everyone can appreciate that mine are always the best :D
BTW Only the VBA IIf() function evaluates both sides. The SQL IIf() function is optimised and doesn't suffer from this performance (and logic) penalty.

[ADezii]

Oh well, multiple solutions and ideas is always worthwhile anyway, as not everyone can appreciate that mine are always the best :D
BTW Only the VBA IIf() function evaluates both sides. The SQL IIf() function is optimised and doesn't suffer from this performance (and logic) penalty.

Still sticking by the IIF(), are we? (LOL).

[NeoPa]

Not at all. My solution would use Nz(). However, Michelle's solution would not have suffered by using the IIf() as it was in SQL.
I try to use what is appropriate for the occasion (none of these anti-function prejudices for me :D)