none
Find highest feet and Inches RRS feed

  • Question

  • Have a database with six different feet and inches per row. I am using a very complex query to find the highest feet and inches.

    example:

    SELECT Student.ID, Student.InMeet, Student.Student, schools.School, Student.PR, Jumps.dis_ft_1, Jumps.dis_in_1, Jumps.dis_ft_2, Jumps.dis_in_2, Jumps.dis_ft_3, Jumps.dis_in_3, Jumps.dis_ft_4, Jumps.dis_in_4, Jumps.dis_ft_5, Jumps.dis_in_5, Jumps.dis_ft_6, Jumps.dis_in_6, [dis_ft_1]*12+[dis_in_1] AS totalin1, ([dis_ft_2]*12)+[dis_in_2] AS totalin2, ([dis_ft_3]*12)+[dis_in_3] AS totalin3, ([dis_ft_4]*12)+[dis_in_4] AS totalin4, ([dis_ft_5]*12)+[dis_in_5] AS totalin5, ([dis_ft_6]*12)+[dis_in_6] AS totalin6, Maximum([totalin1],[totalin2],[totalin3],[totalin4],[totalin5],[totalin6]) AS longest, Int([longest]/12) AS longft, ([longest]/12-Int([longest]/12))*12 AS longin
    FROM schools INNER JOIN (Student LEFT JOIN Jumps ON Student.ID = Jumps.student_id) ON schools.ID = Student.School
    WHERE (((Student.InMeet)=True))
    ORDER BY Student.ID;

    Is there any to do this without converting every jump to inches?


    Gary

    Wednesday, November 4, 2015 12:49 PM

Answers

  • Option Compare Database
    Option Explicit
    
    Function FeetToInches(varFeet As Variant, Optional varInches As Variant = 0) As Double
        
        Dim dblFeet As Double
        Dim dblInch As Double
        Dim dblReturn As Double
        
        dblReturn = 0
        dblFeet = 0
        dblInch = 0
        
        dblFeet = Val(Nz(varFeet, 0))
        dblInch = Val(Nz(varInches, 0))
        
        dblReturn = dblFeet * 12 + dblInch
        
        FeetToInches = dblReturn
        
    End Function
    
    Function InchesToFtIn(varInches As Variant) As String
    
        Dim dblFeet As Double
        Dim dblInch1 As Double
        Dim dblInch2 As Double
        Dim strReturn As String
        
        strReturn = ""
        dblFeet = 0
        dblInch1 = 0
        dblInch2 = 0
    
        dblInch1 = Val(Nz(varInches, 0))
        
        dblFeet = dblInch1 \ 12
        dblInch2 = (dblInch1 / 12 - dblInch1 \ 12) * 12
        
        strReturn = dblFeet & "'" & dblInch2 & Chr(34)
        
        InchesToFtIn = strReturn
        
    End Function
    
    Function InchesToFeet(varInches As Variant, Optional ReturnRemainder As Boolean) As Double
    
        Dim dblReturn As Double
        Dim dblInch As Double
        
        dblReturn = 0
        dblInch = 0
    
        dblInch = Val(Nz(varInches, 0))
        
        If (Not ReturnRemainder) Then
            dblReturn = dblInch \ 12
        Else
            dblReturn = (dblInch / 12 - dblInch \ 12) * 12
        End If
    
        InchesToFeet = dblReturn
        
    End Function

    • Marked as answer by gehrenfeld1 Friday, November 6, 2015 12:26 PM
    Thursday, November 5, 2015 7:16 PM
  • Examples:

    ?FeetToInches(1,6)
     18

    ?FeetToInches(1,2.75)
     14.75

    ?InchesToFeet(14.75,true)
     2.75

    ?InchesToFeet(14.75,False)
     1

    ?InchesToFeet(14.75)
     1

    ?InchesToFtIn(14.75)
    1'2.75"

    • Marked as answer by gehrenfeld1 Thursday, November 5, 2015 7:29 PM
    Thursday, November 5, 2015 7:23 PM
  • That did the trick.

    I am 69 Years Old and I have learned so much from you. Just proves your never to old to learn.

    Thanks to everyone for all the help 


    Gary

    • Marked as answer by gehrenfeld1 Friday, November 6, 2015 12:25 PM
    Thursday, November 5, 2015 7:28 PM

All replies

  • Is there any to do this without converting every jump to inches?

    Hi Gary,

    You could store the result in inches. Then for display purposes you can convert the inches-value to feet.

    Something comparable I use for my Running database. Time is entered as e.g.  12345 (in hhmmss notation). This is converted to 5625 (seconds) for storage, calculation, sorting. And for display purposes it is converted to 1:23:45.

    You could use two simple functions   Foot_to_inch (nr_feet)  and Inch_to foot (nr_inches)

    Imb.

    Wednesday, November 4, 2015 1:14 PM
  • The problem is the official high school tape measure we use to measure the jump doesn't show total inches just feet and inches.



    Gary

    Wednesday, November 4, 2015 1:24 PM
  • Hi Gary,

    It would better if your database was properly normalized.  That would mean that your table Jumps would have only 5 fields:

    Table: Jumps
    ID
    StudentID
    Index
    DistanceFeet
    DistanceInches

    You would then have a record for each jump by student and the Index field would indicate the order of each jump.

    However, your current setup can be managed just as well.

    What you need to do first is create what's known as an union query.  See the following:

    SELECT Jumps.Student_ID, 1 As Jump, Jumps.dis_ft_1 As DistFt, Jumps.dis_in_1 As DistIn FROM Jumps; UNION ALL SELECT Jumps.Student_ID, 2, Jumps.dis_ft_2, Jumps.dis_in_2 FROM Jumps; UNION ALL SELECT Jumps.Student_ID, 3, Jumps.dis_ft_3, Jumps.dis_in_3 FROM Jumps; UNION ALL SELECT Jumps.Student_ID, 4, Jumps.dis_ft_4, Jumps.dis_in_4 FROM Jumps; UNION ALL SELECT Jumps.Student_ID, 5, Jumps.dis_ft_5, Jumps.dis_in_5 FROM Jumps; UNION ALL

    SELECT Jumps.Student_ID, 6, Jumps.dis_ft_6, Jumps.dis_in_6 FROM Jumps ORDER BY 1, 2;

    Save this query as "uni_Jumps."

    Now we will create a select query to get your desired results.  See the following:

    SELECT Student.ID, Student.Student, Student.School, uni_Jumps.DistFt, uni_Jumps.DistIn, [DistFt]*12+[DistIn] AS TotalInches
    FROM School INNER JOIN (Student INNER JOIN uni_Jumps ON Student.ID = uni_Jumps.Student_ID) ON School.ID = Student.School
    WHERE ((([DistFt]*12+[DistIn]) In (SELECT Max([DistFt]*12+[DistIn]) AS Inches
    FROM uni_Jumps;)));

    Now save this query as "sel_StudentJump."  After you've saved the query, go ahead and run it. 

    That's all there is to it.

    Post back if you have any questions.



    • Edited by RunningManHD Wednesday, November 4, 2015 3:56 PM
    Wednesday, November 4, 2015 3:52 PM
  • The problem is the official high school tape measure we use to measure the jump doesn't show total inches just feet and inches.

    Hi Gary,

    You can make a simple function to convert feet/inches to inches.

        Function Foot_to_inch(cur_feet As Integer, cur_inches As Integer) As Integer
            Foot_to_inch = 12 * cur_feet + cur_inches
        End Function

    Eventually you can modify/extend this for all kind of purposes.

    Imb.

    Wednesday, November 4, 2015 4:03 PM
  • How would I used this function when using the form add jumps?

    The input fields for the jump is dis_1_ft and dis_1_in. Then the next jump would have the fields dis_2_ft and dis_2_in and so on.


    Gary

    Wednesday, November 4, 2015 4:39 PM
  • I ran the function and it works. 

    Thank you


    Gary


    • Edited by gehrenfeld1 Wednesday, November 4, 2015 5:04 PM
    Wednesday, November 4, 2015 4:57 PM
  • I agree but when I did that I could figure out how to do a form with displaying jump1 through jump6  in a single row.

    Gary

    Thursday, November 5, 2015 12:16 AM
  • I agree but when I did that I could figure out how to do a form with displaying jump1 through jump6  in a single row.

    Gary


    I am going to assume that you are responding to my earlier post regarding your current table structure and a normalized structure.  To view your records in row format as you have it your current table, you would just use what's known as a crosstab query.  If you would like examples, I will be happy to provide them.
    Thursday, November 5, 2015 1:16 PM
  • Have a database with six different feet and inches per row. I am using a very complex query to find the highest feet and inches.

    example:

    SELECT Student.ID, Student.InMeet, Student.Student, schools.School, Student.PR, Jumps.dis_ft_1, Jumps.dis_in_1, Jumps.dis_ft_2, Jumps.dis_in_2, Jumps.dis_ft_3, Jumps.dis_in_3, Jumps.dis_ft_4, Jumps.dis_in_4, Jumps.dis_ft_5, Jumps.dis_in_5, Jumps.dis_ft_6, Jumps.dis_in_6, [dis_ft_1]*12+[dis_in_1] AS totalin1, ([dis_ft_2]*12)+[dis_in_2] AS totalin2, ([dis_ft_3]*12)+[dis_in_3] AS totalin3, ([dis_ft_4]*12)+[dis_in_4] AS totalin4, ([dis_ft_5]*12)+[dis_in_5] AS totalin5, ([dis_ft_6]*12)+[dis_in_6] AS totalin6, Maximum([totalin1],[totalin2],[totalin3],[totalin4],[totalin5],[totalin6]) AS longest, Int([longest]/12) AS longft, ([longest]/12-Int([longest]/12))*12 AS longin
    FROM schools INNER JOIN (Student LEFT JOIN Jumps ON Student.ID = Jumps.student_id) ON schools.ID = Student.School
    WHERE (((Student.InMeet)=True))
    ORDER BY Student.ID;

    Is there any to do this without converting every jump to inches?


    Gary

    In your original post, you wanted to know if there was an easier way to write your query without converting every field for feet and inches to just inches.  My original post gives you the means to do that using your current structure.  The union query will covert your jump records into a single record for each student and jump.  Everything else you do can be based off of the union query where you only need a conversion for one field.  For that matter, the union query can handle the conversion for you prior to any follow up queries.

    SELECT Jumps.Student_ID, 1 As Jump, Jumps.dis_ft_1 As DistFt, Jumps.dis_in_1 As DistIn, 12*Jumps.dis_ft_1+Jumps.dis_in_1 As TotalInches
    FROM Jumps;
    
    UNION ALL
    
    SELECT Jumps.Student_ID, 2, Jumps.dis_ft_2, Jumps.dis_in_2, 12*Jumps.dis_ft_2+Jumps.dis_in_2 As TotalInches
    FROM Jumps;
    
    UNION ALL
    
    SELECT Jumps.Student_ID, 3, Jumps.dis_ft_3, Jumps.dis_in_3, 12*Jumps.dis_ft_3+Jumps.dis_in_3 As TotalInches
    FROM Jumps;
    
    UNION ALL
    
    SELECT Jumps.Student_ID, 4, Jumps.dis_ft_4, Jumps.dis_in_4, 12*Jumps.dis_ft_4+Jumps.dis_in_4 As TotalInches
    FROM Jumps;
    
    UNION ALL
    
    SELECT Jumps.Student_ID, 5, Jumps.dis_ft_5, Jumps.dis_in_5, 12*Jumps.dis_ft_5+Jumps.dis_in_5 As TotalInches
    FROM Jumps;
    
    UNION ALL SELECT Jumps.Student_ID, 6, Jumps.dis_ft_6, Jumps.dis_in_6, 12*Jumps.dis_ft_6+Jumps.dis_in_6 As TotalInches
    FROM Jumps
    ORDER BY 1, 2;
    

    The results of this query will look like the following:

    The select query that I provided for you in the first post will give the student, or students if tied, who had the best jump distance and the distance value.

    This next select query is based off of the original union query I provided and will give you all the students and their best distance.

    SELECT Student.Student, Student.School, Max([DistFt]*12+[DistIn]) AS TotalInches
    FROM School INNER JOIN (Student INNER JOIN uni_Jumps ON Student.ID = uni_Jumps.Student_ID) ON School.ID = Student.School
    GROUP BY Student.Student, Student.School;
    

    The results of this query will look like the following:

    Thursday, November 5, 2015 1:37 PM
  • I will give that a try.

    Thank you big time!!!!


    Gary

    Thursday, November 5, 2015 1:48 PM
  • The problem is the official high school tape measure we use to measure the jump doesn't show total inches just feet and inches.

    Hi Gary,

    You can make a simple function to convert feet/inches to inches.

        Function Foot_to_inch(cur_feet As Integer, cur_inches As Integer) As Integer
            Foot_to_inch = 12 * cur_feet + cur_inches
        End Function

    Eventually you can modify/extend this for all kind of purposes.

    Imb.

    Though this may be a better method for calculating the distance in total inches, it does not answer the question asked.  The problem was to find an easier method for querying without having to convert every distance field into total inches.

    As it goes, the function as it is only replicates the OP's expressions in his current query.  What would be better is error handling for missing or invalid values, then a function such as this gains value.

    Option Compare Database
    Option Explicit
    
    Function FeetToInches(varFeet As Variant, Optional varInches As Variant = 0) As Integer
        
        Dim intFeet As Integer
        Dim intInch As Integer
        Dim intReturn As Integer
        
        intReturn = 0
        intFeet = 0
        intInch = 0
        
        intFeet = Val(Nz(varFeet, 0))
        intInch = Val(Nz(varInches, 0))
        
        intReturn = intFeet * 12 + intInch
        
        FeetToInches = intReturn
        
    End Function
    
    Function InchesToFtIn(varInches As Variant) As String
    
        Dim intFeet As Integer
        Dim intInch1 As Integer
        Dim intInch2 As Integer
        Dim strReturn As String
        
        strReturn = ""
        intFeet = 0
        intInch1 = 0
        intInch2 = 0
    
        intInch1 = Val(Nz(varInches, 0))
        
        intFeet = intInch1 \ 12
        intInch2 = intInch1 Mod 12
        
        strReturn = intFeet & "'" & intInch2 & Chr(34)
        
        InchesToFtIn = strReturn
        
    End Function
    
    Function InchesToFeet(varInches As Variant, Optional ReturnRemainder As Boolean) As Integer
    
        Dim intReturn As Integer
        Dim intInch As Integer
        
        intReturn = 0
        intInch = 0
    
        intInch = Val(Nz(varInches, 0))
        
        If (Not ReturnRemainder) Then
            intReturn = intInch \ 12
        Else
            intReturn = intInch Mod 12
        End If
    
        InchesToFeet = intReturn
        
    End Function


    Thursday, November 5, 2015 2:15 PM
  • Wow those function works really well.

    The only problem I see is when the inches is 11.75 it only shows 11.

    Hopefully that is a easy fix.

    Thanks


    Gary

    Thursday, November 5, 2015 2:46 PM
  • Wow those function works really well.

    The only problem I see is when the inches is 11.75 it only shows 11.

    Hopefully that is a easy fix.

    Thanks


    Gary


    The problem you describe is a whole number and fraction, not feet and inches.
    Thursday, November 5, 2015 2:50 PM
  • So no way to display 11.75. Bummer because your functions are just what I was looking for.

    Thank you very much for your help.


    Gary

    Thursday, November 5, 2015 2:55 PM
  • If you want to convert feet and inches to a fraction/decimal value, that can be done.  It just isn't in the procedures provided here and isn't part of your original requirement.
    Thursday, November 5, 2015 3:14 PM
  • I apologize for not being more clear in my original requirement. As you can tell I am new at this.

    Can you lease provide me with what I need to do to convert the inches so they display with a format of either 11.75 or 11 3/4.

    I would prefer 11.75

    Again sorry

     

    Gary



    • Edited by gehrenfeld1 Thursday, November 5, 2015 3:23 PM
    Thursday, November 5, 2015 3:21 PM
  • I changed 

    intInch2 = intInch1 Mod 12 to (intInch1 / 12 - Int(intInch1 / 12)) * 12

    but it still show only 11 vise 11.75


    Gary

    Thursday, November 5, 2015 5:01 PM
  • I changed 

    intInch2 = intInch1 Mod 12 to (intInch1 / 12 - Int(intInch1 / 12)) * 12

    but it still show only 11 vise 11.75


    Gary

    Hi Gary,

    For decimal numbers you can better use the Single or Double datatype.

    Imb.

    Thursday, November 5, 2015 5:12 PM
  • Function InchesToFtIn(varInches As Variant) As String

        Dim intFeet As Double
        Dim intInch1 As Double
        Dim intInch2 As Double
        Dim strReturn As String

        strReturn = ""
        intFeet = 0
        intInch1 = 0
        intInch2 = 0

        intInch1 = Val(Nz(varInches, 0))

        intFeet = intInch1 \ 12
        intInch2 = (intInch1 / 12 - Int(intInch1 / 12)) * 12

        strReturn = intFeet & "'" & intInch2 & Chr(34)

        InchesToFtIn = strReturn

    Still doesn't work. Sorry for being a pain. 

                    

    Gary

    Thursday, November 5, 2015 5:23 PM
  • Option Compare Database
    Option Explicit
    
    Function FeetToInches(varFeet As Variant, Optional varInches As Variant = 0) As Double
        
        Dim dblFeet As Double
        Dim dblInch As Double
        Dim dblReturn As Double
        
        dblReturn = 0
        dblFeet = 0
        dblInch = 0
        
        dblFeet = Val(Nz(varFeet, 0))
        dblInch = Val(Nz(varInches, 0))
        
        dblReturn = dblFeet * 12 + dblInch
        
        FeetToInches = dblReturn
        
    End Function
    
    Function InchesToFtIn(varInches As Variant) As String
    
        Dim dblFeet As Double
        Dim dblInch1 As Double
        Dim dblInch2 As Double
        Dim strReturn As String
        
        strReturn = ""
        dblFeet = 0
        dblInch1 = 0
        dblInch2 = 0
    
        dblInch1 = Val(Nz(varInches, 0))
        
        dblFeet = dblInch1 \ 12
        dblInch2 = (dblInch1 / 12 - dblInch1 \ 12) * 12
        
        strReturn = dblFeet & "'" & dblInch2 & Chr(34)
        
        InchesToFtIn = strReturn
        
    End Function
    
    Function InchesToFeet(varInches As Variant, Optional ReturnRemainder As Boolean) As Double
    
        Dim dblReturn As Double
        Dim dblInch As Double
        
        dblReturn = 0
        dblInch = 0
    
        dblInch = Val(Nz(varInches, 0))
        
        If (Not ReturnRemainder) Then
            dblReturn = dblInch \ 12
        Else
            dblReturn = (dblInch / 12 - dblInch \ 12) * 12
        End If
    
        InchesToFeet = dblReturn
        
    End Function

    • Marked as answer by gehrenfeld1 Friday, November 6, 2015 12:26 PM
    Thursday, November 5, 2015 7:16 PM
  • Examples:

    ?FeetToInches(1,6)
     18

    ?FeetToInches(1,2.75)
     14.75

    ?InchesToFeet(14.75,true)
     2.75

    ?InchesToFeet(14.75,False)
     1

    ?InchesToFeet(14.75)
     1

    ?InchesToFtIn(14.75)
    1'2.75"

    • Marked as answer by gehrenfeld1 Thursday, November 5, 2015 7:29 PM
    Thursday, November 5, 2015 7:23 PM
  • That did the trick.

    I am 69 Years Old and I have learned so much from you. Just proves your never to old to learn.

    Thanks to everyone for all the help 


    Gary

    • Marked as answer by gehrenfeld1 Friday, November 6, 2015 12:25 PM
    Thursday, November 5, 2015 7:28 PM
  • Not a problem, but please mark the one above as the answer as it is the true solution to your question.  Glad to help.
    Thursday, November 5, 2015 7:38 PM
  • Here's one more for you...

    Function InchesToFeetDec(varInches As Variant) As Double
    
        Dim dblReturn As Double
        Dim dblInch As Double
        
        dblReturn = 0
        dblInch = 0
    
        dblInch = Val(Nz(varInches, 0))
        
        dblReturn = dblInch / 12
    
        InchesToFeetDec = dblReturn
        
    End Function

    Examples:

    ?InchesToFeetDec(10.75)
     0.895833333333333

    ?InchesToFeetDec(14.75)
     1.22916666666667

    Thursday, November 5, 2015 7:47 PM