By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Make table from a query

P: 38
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.
Sep 20 '07 #1
Share this Question
Share on Google+
18 Replies


Expert 100+
P: 296
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....

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM yourTableName
  3. 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));
Sep 20 '07 #2

ADezii
Expert 5K+
P: 8,597
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Ver_Number, Date1, Date2, Date3, Date4, Date5, fCalculateFinalDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS Final_Date INTO tblNewFinalDate
  2. FROM tblFinalDate;
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
  2. If IsNull(Date_1) Then
  3.   If IsNull(Date_2) Then
  4.     If IsNull(Date_3) Then
  5.       If IsNull(Date_4) Then
  6.         If IsNull(Date_5) Then
  7.           fCalculateFinalDate = Null
  8.         End If
  9.       Else
  10.         fCalculateFinalDate = Date_4
  11.       End If
  12.     Else
  13.       fCalculateFinalDate = Date_3
  14.     End If
  15.   Else
  16.     fCalculateFinalDate = Date_2
  17.   End If
  18. Else
  19.   fCalculateFinalDate = Date_1
  20. End If
  21. End Function
Sep 20 '07 #3

Expert 100+
P: 296
Expand|Select|Wrap|Line Numbers
  1. SELECT Ver_Number, Date1, Date2, Date3, Date4, Date5, fCalculateFinalDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS Final_Date INTO tblNewFinalDate
  2. FROM tblFinalDate;
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
  2. If IsNull(Date_1) Then
  3.   If IsNull(Date_2) Then
  4.     If IsNull(Date_3) Then
  5.       If IsNull(Date_4) Then
  6.         If IsNull(Date_5) Then
  7.           fCalculateFinalDate = Null
  8.         End If
  9.       Else
  10.         fCalculateFinalDate = Date_4
  11.       End If
  12.     Else
  13.       fCalculateFinalDate = Date_3
  14.     End If
  15.   Else
  16.     fCalculateFinalDate = Date_2
  17.   End If
  18. Else
  19.   fCalculateFinalDate = Date_1
  20. End If
  21. 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.
Sep 20 '07 #4

P: 38
fd1
Try something like this....

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM yourTableName
  3. 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.
Sep 20 '07 #5

P: 38
fd1
Thanks ADezii, that worked too
how can I tweak your code to not include the "ver_number" that has all 5 dates null?
Sep 21 '07 #6

P: 38
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
Sep 21 '07 #7

ADezii
Expert 5K+
P: 8,597
Thanks ADezii, that worked too
how can I tweak your code to not include the "ver_number" that has all 5 dates null?
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblNewFinalDate ( Ver_Number, Date1, Date2, Date3, Date4, Date5, Final_Date )
  2. SELECT Ver_Number, Date1, Date2, Date3, Date4, Date5, fCalculateFinalDate([Date1],[Date2],[Date3],[Date4],[Date5]) AS Final_Date
  3. FROM tblFinalDate
  4. 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;
Sep 21 '07 #8

ADezii
Expert 5K+
P: 8,597
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
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
  2. If IsNull(Date_1) Then
  3.   If IsNull(Date_2) Then
  4.     If IsNull(Date_3) Then
  5.       If IsNull(Date_4) Then
  6.         If IsNull(Date_5) Then
  7.           fCalculateFinalDate = Null
  8.         Else
  9.           fCalculateFinalDate = Date_5
  10.         End If
  11.       Else
  12.         fCalculateFinalDate = Date_4
  13.       End If
  14.     Else
  15.       fCalculateFinalDate = Date_3
  16.     End If
  17.   Else
  18.     fCalculateFinalDate = Date_2
  19.   End If
  20. Else
  21.   fCalculateFinalDate = Date_1
  22. End If
  23. End Function
Sep 21 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
Late to the party here, but wouldn't Nz() make life a lot easier for the dates?
Sep 21 '07 #10

Expert 100+
P: 296
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.
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Table1
  3. 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
Sep 21 '07 #11

P: 38
fd1
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateFinalDate(Date_1, Date_2, Date_3, Date_4, Date_5) As Variant
  2. If IsNull(Date_1) Then
  3.   If IsNull(Date_2) Then
  4.     If IsNull(Date_3) Then
  5.       If IsNull(Date_4) Then
  6.         If IsNull(Date_5) Then
  7.           fCalculateFinalDate = Null
  8.         Else
  9.           fCalculateFinalDate = Date_5
  10.         End If
  11.       Else
  12.         fCalculateFinalDate = Date_4
  13.       End If
  14.     Else
  15.       fCalculateFinalDate = Date_3
  16.     End If
  17.   Else
  18.     fCalculateFinalDate = Date_2
  19.   End If
  20. Else
  21.   fCalculateFinalDate = Date_1
  22. End If
  23. End Function
Thanks ADezii, that seems to work now
Sep 21 '07 #12

P: 38
fd1
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Table1
  3. 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.
Sep 21 '07 #13

ADezii
Expert 5K+
P: 8,597
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().
Sep 21 '07 #14

ADezii
Expert 5K+
P: 8,597
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.
Sep 21 '07 #15

Expert 100+
P: 296
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!
Sep 21 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 22 '07 #17

ADezii
Expert 5K+
P: 8,597
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).
Sep 22 '07 #18

NeoPa
Expert Mod 15k+
P: 31,186
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)
Sep 22 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.