Find Min of several fields on a report 1

[MorningSun]

I'm sure this is easier than I am making it. I have 5 separate fields on a report. I need to find the min value of the 5 fields. Since they are in 5 different fields, the Min(field) expression will not work.

Fields are Factor1, Factor2, Factor3, Factor4, Factor5
Min is MinZeroFactor

Any suggestions?
Thank you!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[Remou]

The first value in the list returned by this is the minimum value:

Function SortList(strValueList)
'Modified from: [URL unfurl="true"]http://support.microsoft.com/kb/246067[/URL]
  
  Dim x, y
  Dim strDict

  strDict = Split(strValueList, ";")
  
  ' we need more than one item to warrant sorting
  If UBound(strDict) > 1 Then

    ' perform a a shell sort of the string array
    For x = 0 To UBound(strDict)
      For y = x To UBound(strDict)
        If Val(strDict(x)) > Val(strDict(y)) Then
            strItem = strDict(x)
            strDict(x) = strDict(y)
            strDict(y) = strItem
        End If
      Next
    Next

    For x = 0 To UBound(strDict)
      strList = strList & ";" & strDict(x)
    Next

    SortValueList = Mid(strList, 2)
  End If

End Function

 

[dhookom]

Have you considered normalizing your table structure? I would create a query like

SELECT  Factor1 as Factor, 1 as FacNum
FROM tblA
UNION ALL
SELECT Factor2, 2
FROM tblA
UNION ALL
SELECT Factor3, 3
FROM tblA
UNION ALL
SELECT Factor4, 4
FROM tblA
UNION ALL
SELECT Factor5, 5
FROM tblA;

I expect you would add some other primary key field from the original table. You can then find the minimum using a standard group by query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MorningSun]

Normalizing using a query is working like a charm but how do I add data from another table? Say I want to add Manager and Location from tblB to this data so that my fields would include Factor, FacNum, Manager, Location...??

SELECT Factor1 as Factor, 1 as FacNum
FROM tblA
UNION ALL
SELECT Factor2, 2
FROM tblA
UNION ALL
SELECT Factor3, 3
FROM tblA
UNION ALL

THANK YOU SO MUCH!




PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[dhookom]

How is tblA related to tblB? Does tblA have a primary key field?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MorningSun]

Yes, tblA is related to tblB by AuditNo in a 1 to many relationship...



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[dhookom]

Then I think you need to add AuditNo to the union query:

SELECT AuditNo, Factor1 as Factor, 1 as FacNum
FROM tblA
UNION ALL
SELECT AuditNo, Factor2, 2
FROM tblA
UNION ALL
SELECT AuditNo, Factor3, 3
FROM tblA
UNION ALL
--- etc ---

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MorningSun]

I do have AuditNo in my query (sorry I didn't have that above) but I can't figure out how to add fields from tblB to my query. I have it set up like you list above but I want to also ionclude Manager & Location from tblB based on the primary key of AuditNo.



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[dhookom]

Create a new query based on the union query and tblB. Join the AuditNo fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MorningSun]

See, I knew I was making this harder than it had to be!
Thank you so much! You've made my impossible job so much easier!!!!!



PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.