DbStudent Dec 5, 2009   8:24 PM GMT

1. There are various ways (via code or by query) to solve this problem, regardless of whether the schema meets third normal form, or is changed as described below. There are also other important issues to consider before automatically assuming that your schema should be rewritten into separate tables. 2. As for a query-based solution, there may be very well be some arcane SQL expression that compares and selects a min or max value from a set of columns of similar data types in a single row. I'd review some SQL reference books to see if such a query exists. If not, and if you retained your single-table schema, I think you'd have to use a query to create a temporary schema that emulates the three table approach described in par. 5, below. 3. As for a code solution, I would create a recordset, iterate through each record, place the five dates from each record into an array, sort the array and get the date value of the first item in the array (or just directly get the highest value in the array). This would handy if you wanted to record the result in another table or use it in your application logic. 4. AS TO THE NORMAL FORM OF YOUR SCHEMA: I don't agree that the use of 12 date columns by itself NECESSARILY violates third normal form. If D1 through D12 represent unique types of information (such as a fixed and known set of milestones in some process), then the table is normalized. This would be true even if a very large number of milestone types were being modeled, so long as those types are not redundant in any way. (An example of redundant columns would be current and former addresses, which are better handled in a detail table with start and end date fields.) Of course, if the number of milestone types was large, e.g., D1-D324, then putting those types in a separate table and recording specific milestone dates in a child table, as described below, would be appropriate way to improve performance and might even be necessary to stay within the table design specs of the database system (i.e., max. no. o f columns). 5. On the other hand, if the number of milestone types was or might be variable, or is unknown, then the schema would not be normalized, and should be revised to include at least three tables: The original table, a milestone types table and a milestone dates child table, with pointers back to the first two tables. But a multi table approach poses other design issues: First, does the app require that for each record in the original master table, a milestone date for each type of milestone be recorded? Second, would child records be required or permitted to be initialized or later set to NULL for any purpose? These activities are in addition to your original issue, which would require a query to find the highest date value among the D1-D12 child records for a given parent record in the original table. 6. Relatively trivial denormalization is acceptable when required to improve performance or convenience. For example, if your app requires that some or all of the D1-D12 values be entered in order to create a valid record, your single table approach works beautifully because you can easily indicate which fields must be entered (and set default values). It also makes it easier to write code behind a form (or maybe a table macro under Access 2010) to apply business rules that govern the relative values of D1-D12 (e.g., D5 must be later than D8). Not knowing what your app does, I can't say whether any performance benefit would be obtained. If there is even a small chance that the number of milestone types might changed to some unknown value greater than 12, then I think I'd use the multitable approach in order to future proof the application. It might be alot more work now, but it would require very little additional work when and if the number of milestone types had to be changed. Hope this helps. Good luck!

10 pointsBadges:

Meandyou Dec 24, 2009   3:11 PM GMT

regarding DbStudent's comment #4 ... I would agree that having 12 dates in one row does not NECESSARILY violate first normal form. However, when one is comparing these dates to each other it seems to imply that they are signigant to each other which implies they are repeating columns.

5,220 pointsBadges:

philpl1jb Aug 19, 2013   7:28 PM GMT

or perhaps this could work ...

 
Select field1, field2, 
 Case 
   when d1 > d2 and d1 > d3 and d1 > d4 
     and d1 > d5 and d1 > d6 and d1 > d7 
     and d1 > d8 and d1 > d9 and d1 > d10 
     and d1 > d11 and d1 > d12 then d1

   when d2 > d3 and d2 > d4 
     and d2 > d5 and d2 > d6 and d2 > d7 
     and d2 > d8 and d2 > d9 and d2 > d10 
     and d2 > d11 and d2 > d12 then d2
  
  when d3 > d4 
     and d3 > d5 and d3 > d6 and d3 > d7 
     and d3 > d8 and d3 > d9 and d3 > d10 
     and d3 > d11 and d3 > d12 then d3

... more of the same until 

   else d12

   from myfile

54,090 pointsBadges:

philpl1jb Aug 19, 2013   7:33 PM GMT

Don't know why I just worked on a question from 2009 .. would like to retract my answer.

54,090 pointsBadges: