Ok. First I changed your sample data to actually get data that was in the range of your tblMale_Standars (by the way you can put both female and male standards in the same table, just put in an additional field with the gender so and create another link via gender in the queries below to make it work)
For person 1 I have HIP 15.6, Waist 20, Neck 15
For person 2 I have HIP 15.8, Waist 21, Neck 18
First I created this query:
Code:
[SELECT tblBCP.BCPID, tblBCP.HEIGHT, tblBCP.HIP, tblBCP.WAIST, tblBCP.NECK, [hip]+[waist]-[neck] AS Circum, Int([hip]+[waist]-[neck]) AS CircumCalc, IIf([circum]-[circumcalc]>0.25 And [circum]-[circumcalc]<0.75,0.5,IIf([circum]-[circumcalc]>=0.75,1,0)) AS Remainder, [circumcalc]+[remainder] AS Final, (Int([hip]+[waist]-[neck]))+(IIf(([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))>0.25 And ([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))<0.75,0.5,IIf(([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))>=0.75,1,0))) AS FinalFullCalc
FROM tblBCP;
I left a bunch of calculations in there to help you see how I put the final calculation together for the circumference you use to look up values in your male standards table.
CALL THIS QUERY qryCircum
Second, create this query:
Code:
SELECT QryCircum.BCPID, QryCircum.HEIGHT, QryCircum.HIP, QryCircum.WAIST, QryCircum.NECK, QryCircum.Circum, QryCircum.CircumCalc, QryCircum.Remainder, QryCircum.Final, QryCircum.FinalFullCalc, tlbMale_Standards.Body_Fat_Percentage
FROM QryCircum LEFT JOIN tlbMale_Standards ON (QryCircum.FinalFullCalc = tlbMale_Standards.Circumference_Value) AND (QryCircum.HEIGHT = tlbMale_Standards.HEIGHT);
You can call this query whatever you want.
you can see that person 1 and 2 now have a looked up BMI value where person 6 does not because there was no matching data in your male standards table.