# Find highest feet and Inches

• ### 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

• ```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 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 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 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 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 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 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)) * 12but 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)) * 12but 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 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 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 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