This will be more of a theory question, than a how-to question. I will begin by giving what would be a simple solution, then explain my situation.

EASY: Using a database to track a multi-step process. Having ONE combo box with the different steps of the process as STATUS would be a snap. Look at a form and see the Status, it will show you where in the process this is.

QUESTION: I want one text field in my record for each 'Status' so that I can assign a clerk to perform the step, and to capture the date in which the step is complete.

I've used some nice code, from Microsoft no doubt, to enable me to find the Maximum Date across multiple fields of the same record.

Function Minimum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the smallest. For I = 0 To UBound(FieldArray) If FieldArray(I) < currentVal Then currentVal = FieldArray(I) End If Next I ' Return the minimum value found. Minimum = currentValEnd FunctionFunction Maximum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest. For I = 0 To UBound(FieldArray) If FieldArray(I) > currentVal Then currentVal = FieldArray(I) End If Next I ' Return the maximum value found. Maximum = currentValEnd Function

Function Minimum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the smallest. For I = 0 To UBound(FieldArray) If FieldArray(I) < currentVal Then currentVal = FieldArray(I) End If Next I ' Return the minimum value found. Minimum = currentValEnd FunctionFunction Maximum(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest. For I = 0 To UBound(FieldArray) If FieldArray(I) > currentVal Then currentVal = FieldArray(I) End If Next I ' Return the maximum value found. Maximum = currentValEnd Function

Then put into a query: Maximum Value: Maximum([Field1],[Field2],[Field3])

I want to some how return the field name that has the maximum date.

Second part of this craziness is that in another database I need to show the current status also, but the steps are all different. Some are dates, some are places (txt fields) The only way I can figure out how to show the current status is to show them all and tell my boss that the field that is empty, that is the step of the process it is at.

Does anyone have any ideas to make all of this more simple?

You may want to look at this multi-purpose "row aggregate" function: it can produce any of the traditional aggregates, based on an arbitrary array of input values.

1) Add this UDF to a regular VBA module:

Function RowStats(Stat As String, ParamArray Vars()) ' Function by Patrick Matthews ' This code may be used and distributed freely, so long as you attribute authorship, and indicate ' what URL you found the code at ' This Access UDF calculates various stats for the values passed into the ParamArray. It ' was originally designed for passing several values from a particular row set for ' evaluation; since the values come from the same row, the usual aggregate functions would ' not be appropriate. ' The Stat argument (NOT case sensitive) determines what statistic is calculated: ' "count": count of non-null values ' "min", "max": minimum or maximum ' "sum", "avg": sum or average, excluding strings ' "var", "stdev": sample variance or standard deviation (excluding strings) ' "varp", "stdevp": population variance or standard deviation (excluding strings) ' You may pass any value in the ParamArray. Strings, nulls, and dates are ignored for the ' sum, avg, stdev, stdevp, var, and varp calculations. (Thus, you could use this function to ' add several columns together without wrapping each column with Nz() to handle nulls...) ' If run from VBA, this function appears to accept any number of values in the Vars argument; ' I successfully tested passing several hundred values. If run from the Access query editor, ' the limit appears to be about 28 values. If you need to process more than 28 values, you ' should use the related function RowStatsFieldList. (You can overcome this limit by embedding ' RowStats expressions, but this will produce potentially erroneous results for the Avg, ' StDev[P], or Var[P] stats ' Please note that you can pass arrays as elements of the Vars ParamArray; indeed, the ' RowStatsFieldList function does that ' As with the regular aggregate functions, if all of the parameters are null, then the ' return value is null, except on count, where the return would be zero Dim Numerator As Double Dim Denominator As Double Dim Counter As Long, Counter2 As Long Dim Result As Variant Dim Mean As Double ' Force to upper case to make sure string comparisons are always performed as expected; ' Access uses Option Compare Database by default, but other VBA/VB6 uses binary default Stat = UCase(Stat) Select Case Stat ' In each Case below, loop through the elements of the Vars ParamArray. If the element ' is itself an array, then loop through its elements Case "COUNT" ' Increment the result for each non-null value in the array Result = CLng(0) For Counter = LBound(Vars) To UBound(Vars) If Not IsArray(Vars(Counter)) Then If Not IsNull(Vars(Counter)) Then Result = Result + 1 Else For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter)) If Not IsNull(Vars(Counter)(Counter2)) Then Result = Result + 1 Next End If Next Case "MIN" ' Initialize the result to Null, then check all non-Null values in turn to see ' if it is less. Result = Null For Counter = LBound(Vars) To UBound(Vars) If Not IsArray(Vars(Counter)) Then If IsNull(Result) And Not IsNull(Vars(Counter)) Then Result = Vars(Counter) ElseIf Vars(Counter) < Result Then Result = Vars(Counter) End If Else For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter)) If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then Result = Vars(Counter)(Counter2) ElseIf Vars(Counter)(Counter2) < Result Then Result = Vars(Counter)(Counter2) End If Next End If Next Case "MAX" ' Initialize the result to Null, then check all non-Null values in turn to see ' if it is greater. Result = Null For Counter = LBound(Vars) To UBound(Vars) If Not IsArray(Vars(Counter)) Then If IsNull(Result) And Not IsNull(Vars(Counter)) Then Result = Vars(Counter) ElseIf Vars(Counter) > Result Then Result = Vars(Counter) End If Else For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter)) If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then Result = Vars(Counter)(Counter2) ElseIf Vars(Counter)(Counter2) > Result Then Result = Vars(Counter)(Counter2) End If Next End If Next Case "AVG", "SUM" ' Check each value in turn. If it is numeric, then increment numerator and denominator. ' Divide numerator by denominator to get an average, or by 1 to get the sum. Any Date ' values are coerced into Double For Counter = LBound(Vars) To UBound(Vars) If Not IsArray(Vars(Counter)) Then If IsNumeric(Vars(Counter)) Then Numerator = Numerator + Vars(Counter) Denominator = Denominator + 1 ElseIf IsDate(Vars(Counter)) Then Numerator = Numerator + CDbl(Vars(Counter)) Denominator = Denominator + 1 End If Else For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter)) If IsNumeric(Vars(Counter)(Counter2)) Then Numerator = Numerator + Vars(Counter)(Counter2) Denominator = Denominator + 1 ElseIf IsDate(Vars(Counter)(Counter2)) Then Numerator = Numerator + CDbl(Vars(Counter)(Counter2)) Denominator = Denominator + 1 End If Next End If Next If Denominator > 0 Then Result = Numerator / IIf(Stat = "AVG", Denominator, 1) Else Result = Null End If Case "STDEV", "STDEVP", "VAR", "VARP" ' Take one pass through the set to determine the average, and then determine the ' sum of squared deviances from the mean. Divide by number of elements in the ' array for population or (elements - 1) for sample. If standard deviation, ' take square root. Any Date values are coerced into Double ' This pass generates the numerator and denominator needed for the average For Counter = LBound(Vars) To UBound(Vars) If Not IsArray(Vars(Counter)) Then If IsNumeric(Vars(Counter)) Then Numerator = Numerator + Vars(Counter) Denominator = Denominator + 1 ElseIf IsDate(Vars(Counter)) Then Numerator = Numerator + CDbl(Vars(Counter)) Denominator = Denominator + 1 End If Else For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter)) If IsNumeric(Vars(Counter)(Counter2)) Or IsDate(Vars(Counter)(Counter2)) Then Numerator = Numerator + Vars(Counter)(Counter2) Denominator = Denominator + 1 ElseIf IsDate(Vars(Counter)(Counter2)) Then Numerator = Numerator + CDbl(Vars(Counter)(Counter2)) Denominator = Denominator + 1 End If Next End If Next ' Make sure there are enough numeric elements to avoid a division by zero error. If not, ' return Null If (Stat Like "*P" And Denominator > 0) Or (Not Stat Like "*P" And Denominator > 1) Then Mean = Numerator / Denominator ' This pass sums the squares of the differences between each data point and the mean For Counter = LBound(Vars) To UBound(Vars) If Not IsArray(Vars(0)) Then If IsNumeric(Vars(Counter)) Then Result = Result + (Vars(Counter) - Mean) ^ 2 ElseIf IsDate(Vars(Counter)) Then Result = Result + (CDbl(Vars(Counter)) - Mean) ^ 2 End If Else For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter)) If IsNumeric(Vars(Counter)(Counter2)) Then Result = Result + (Vars(Counter)(Counter2) - Mean) ^ 2 ElseIf IsDate(Vars(Counter)(Counter2)) Then Result = Result + (CDbl(Vars(Counter)(Counter2)) - Mean) ^ 2 End If Next End If Next ' Divide by N for population, and N-1 for sample If Stat Like "*P" Then Result = Result / Denominator Else Result = Result / (Denominator - 1) End If ' Take square root if standard deviation If Stat Like "S*" Then Result = Result ^ 0.5 Else Result = Null End If Case Else ' If Stat is none of the above, then return Null -- invalid Stat Result = Null End Select ' Set return value If Not IsNull(Result) Then RowStats = Result Else RowStats = NullEnd Function

This looks very promising! Give me until tomorrow afternoon to digest this. The reason for my denormalized database is because I'm in the National Guard and the 'Boss' who out ranks me by about 6 ranks said: "I want it done like this, PERIOD" So, I tried and tried, but had to make the design the way he wanted it.

w/o looking at the code, here are a some of my thoughts:

- Each process should have a unique identifier of some type

- There should be a entry created in a separate status table when the status on a process is changed.
The table would have three fields - process id, status, date/time.

- To get the current status of a process, you would use a query similar to this:
select t.pid, t.status, t.datetime from table1 t where t.datetime = (select max(t2.datetime) from table1 t2
where t2.pid = t.pid)

Are you working to mount the dismounted Exchange 2013 database? Then the best course of action is to analyze the causes of Database issue, their probable solutions and decide for the appropriate course of action.

During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written code or looked at an SQL statement before, no problem! ... give i…