Answered by:
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]/12Int([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
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

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

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
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 inchesvalue 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.


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
DistanceInchesYou 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

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 FunctionEventually you can modify/extend this for all kind of purposes.
Imb.
 Proposed as answer by Fei XueMicrosoft employee, Moderator Thursday, November 5, 2015 2:31 AM


I ran the function and it works.
Thank you
Gary
 Edited by gehrenfeld1 Wednesday, November 4, 2015 5:04 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
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. 
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]/12Int([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:


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 FunctionEventually 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
 Edited by RunningManHD Thursday, November 5, 2015 2:16 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.
 Edited by RunningManHD Thursday, November 5, 2015 3:21 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



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 = strReturnStill doesn't work. Sorry for being a pain.
Gary

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

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

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


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