Access Query: evaluate a determined value against age & place %til

  • Thread starter Thread starter Steelghost
  • Start date Start date
S

Steelghost

I need help creating a query in MS Access 2007 that will take a determined
value (FC) and run a query against it showing what percentile the value
should fall into based on person's age? Is there also a way to put actual
parameters defining the expected curve within an access database to be
applied toward the initial question.
 
your question is not answerable.

if you have data in tables it would help to supply a very brief sample
explanation....but otherwise if you are seeking a pure formulaic solution
then one would generally turn to excel.....
 
Hi, I see. More specificially. I have created a Query that will determine a
participant's BMI (Body Mass Index) and would like to know the best way of
using this query to determine what percentile the participant should fall
into. It would also be helpful if I could somehow determine how to break
this BMI down by age group since depending on the age group the mean will be
higher or lower. I'm not sure, if there is even a way to specify the
expected curve or values by age group within Access but would like to know if
anyone can assist me.
 
If you have records with an Age field and wish to know for a given age, a
parameter, what is the percentage of records having that age, or a lower
one, try:

SELECT COUNT(*) / (SELECT COUNT(*) FROM myTable)
FROM myTable
WHERE age <= [Enter age]



Note that the sub-query, (SELECT COUNT(*) FROM myTable), counts the number
of records, in the whole table, while the main COUNT(*) occurs only on
records satisfying the criteria, WHERE age <= [Enter age].



You can run a VBA function you defined yourself, as long as the function is
declared public and in a standard module (not in a class, not under a form):



SELECT MyVBAFunction( COUNT(*) / (SELECT COUNT(*) FROM myTable) )
FROM myTable
WHERE age <= [Enter age]



assuming your VBA function is defined like:


Public Function MyVBAFunction( Argument AS Double) AS double
...
' return the desired value based on the equation of the curve
' you have in mind
...
MyVBAFunction = value_to_return
End Function



Hoping it may help,
Vanderghast, Access MVP
 
You can use range of values, in a table:


FromThis ToThis ReturnThat
0 20 1
20 32 2
32 40 3
.....



You can use:


compare >= FromThis AND compare < ToThis


as in:


SELECT yourtable.*, ranges.ReturnThat
FROM yourTable INNER JOIN ranges
ON ( yourTable.Compare >= ranges.FromThis
AND yourTable.Compare < ranges.ToThis )



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel,

I'll give your suggestions a try and let you know how things turn out.
Michel Walsh said:
If you have records with an Age field and wish to know for a given age, a
parameter, what is the percentage of records having that age, or a lower
one, try:

SELECT COUNT(*) / (SELECT COUNT(*) FROM myTable)
FROM myTable
WHERE age <= [Enter age]



Note that the sub-query, (SELECT COUNT(*) FROM myTable), counts the number
of records, in the whole table, while the main COUNT(*) occurs only on
records satisfying the criteria, WHERE age <= [Enter age].



You can run a VBA function you defined yourself, as long as the function is
declared public and in a standard module (not in a class, not under a form):



SELECT MyVBAFunction( COUNT(*) / (SELECT COUNT(*) FROM myTable) )
FROM myTable
WHERE age <= [Enter age]



assuming your VBA function is defined like:


Public Function MyVBAFunction( Argument AS Double) AS double
...
' return the desired value based on the equation of the curve
' you have in mind
...
MyVBAFunction = value_to_return
End Function



Hoping it may help,
Vanderghast, Access MVP
Steelghost said:
I need help creating a query in MS Access 2007 that will take a determined
value (FC) and run a query against it showing what percentile the value
should fall into based on person's age? Is there also a way to put actual
parameters defining the expected curve within an access database to be
applied toward the initial question.
Click to expand...
Click to expand...
 
Hi Michel,

I tried this but can't get it going for the likes of me, I tried both the
compare statement that you show below and the sql statement , Here's what I
tried...

SELECT Test Results Group 1.* ,ranges.ReturnThat
FROM Test Results Group 1 INNER JOIN ranges
ON ( Test Results Group 1.Compare >= ranges.FromThis
AND Test Results Group 1< ranges.ToThis)

My table that was created looks exactly like yours. What am I missing? I
have created an inner join with the tables but had no luck, tried the compare
statement with the queirs but had no luck going that router either, its
somewhat frustrating, Can you please assist and show me what I'm doing wrong?
 
Since your table name is ill formed, you have to use [ ] around it.


SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1]< ranges.ToThis)





Vanderghast, Access MVP
 
Thanks Michel, I tried this and it worked.

Michel Walsh said:
If you have records with an Age field and wish to know for a given age, a
parameter, what is the percentage of records having that age, or a lower
one, try:

SELECT COUNT(*) / (SELECT COUNT(*) FROM myTable)
FROM myTable
WHERE age <= [Enter age]



Note that the sub-query, (SELECT COUNT(*) FROM myTable), counts the number
of records, in the whole table, while the main COUNT(*) occurs only on
records satisfying the criteria, WHERE age <= [Enter age].



You can run a VBA function you defined yourself, as long as the function is
declared public and in a standard module (not in a class, not under a form):



SELECT MyVBAFunction( COUNT(*) / (SELECT COUNT(*) FROM myTable) )
FROM myTable
WHERE age <= [Enter age]



assuming your VBA function is defined like:


Public Function MyVBAFunction( Argument AS Double) AS double
...
' return the desired value based on the equation of the curve
' you have in mind
...
MyVBAFunction = value_to_return
End Function



Hoping it may help,
Vanderghast, Access MVP
Steelghost said:
I need help creating a query in MS Access 2007 that will take a determined
value (FC) and run a query against it showing what percentile the value
should fall into based on person's age? Is there also a way to put actual
parameters defining the expected curve within an access database to be
applied toward the initial question.
Click to expand...
Click to expand...
 
I understand the ill naming could be part of the problem so I renamed in the
correct format of tblMyTable. However, my disconnect at this point is, after
the table has been created, How do I get the table containing the BMI
measurement to use this "ranges" table to compare the values? I have tried
different methods and came up empty, Can you assist?

Thanks in advance!

Michel Walsh said:
Since your table name is ill formed, you have to use [ ] around it.


SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1]< ranges.ToThis)





Vanderghast, Access MVP


Steelghost said:
Hi Michel,

I tried this but can't get it going for the likes of me, I tried both the
compare statement that you show below and the sql statement , Here's what
I
tried...

SELECT Test Results Group 1.* ,ranges.ReturnThat
FROM Test Results Group 1 INNER JOIN ranges
ON ( Test Results Group 1.Compare >= ranges.FromThis
AND Test Results Group 1< ranges.ToThis)

My table that was created looks exactly like yours. What am I missing? I
have created an inner join with the tables but had no luck, tried the
compare
statement with the queirs but had no luck going that router either, its
somewhat frustrating, Can you please assist and show me what I'm doing
wrong?
Click to expand...
Click to expand...
 
In

SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1].Compare < ranges.ToThis)


I assume the table containing the BMI measurements is [Test Results Group
1]; I assume the field name that holds the value to be compare for the
ranges is name Compare. (It was missing for the comparison with the upper
limit of the range).


Note that if you modify the query to:

SELECT [Test Results Group 1].* , ranges.ReturnThat
FROM [Test Results Group 1], ranges
WHERE ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1].Compare < ranges.ToThis)



you should be able to get the query graphical query editor back.




Hoping it may help,
Vanderghast, Access MVP



Steelghost said:
I understand the ill naming could be part of the problem so I renamed in
the
correct format of tblMyTable. However, my disconnect at this point is,
after
the table has been created, How do I get the table containing the BMI
measurement to use this "ranges" table to compare the values? I have
tried
different methods and came up empty, Can you assist?

Thanks in advance!

Michel Walsh said:
Since your table name is ill formed, you have to use [ ] around it.


SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1]< ranges.ToThis)





Vanderghast, Access MVP


Steelghost said:
Hi Michel,

I tried this but can't get it going for the likes of me, I tried both
the
compare statement that you show below and the sql statement , Here's
what
I
tried...

SELECT Test Results Group 1.* ,ranges.ReturnThat
FROM Test Results Group 1 INNER JOIN ranges
ON ( Test Results Group 1.Compare >= ranges.FromThis
AND Test Results Group 1< ranges.ToThis)

My table that was created looks exactly like yours. What am I missing?
I
have created an inner join with the tables but had no luck, tried the
compare
statement with the queirs but had no luck going that router either, its
somewhat frustrating, Can you please assist and show me what I'm doing
wrong?
:

You can use range of values, in a table:


FromThis ToThis ReturnThat
0 20 1
20 32 2
32 40 3
.....



You can use:


compare >= FromThis AND compare < ToThis


as in:


SELECT yourtable.*, ranges.ReturnThat
FROM yourTable INNER JOIN ranges
ON ( yourTable.Compare >= ranges.FromThis
AND yourTable.Compare < ranges.ToThis )



Hoping it may help,
Vanderghast, Access MVP


Hi, I see. More specificially. I have created a Query that will
determine a
participant's BMI (Body Mass Index) and would like to know the best
way
of
using this query to determine what percentile the participant should
fall
into. It would also be helpful if I could somehow determine how to
break
this BMI down by age group since depending on the age group the mean
will
be
higher or lower. I'm not sure, if there is even a way to specify
the
expected curve or values by age group within Access but would like
to
know
if
anyone can assist me.

:

I need help creating a query in MS Access 2007 that will take a
determined
value (FC) and run a query against it showing what percentile the
value
should fall into based on person's age? Is there also a way to put
actual
parameters defining the expected curve within an access database to
be
applied toward the initial question.
Click to expand...
Click to expand...
Click to expand...
 
I have tried everything but still can not get my exisiting tables to use the
values that I expect to show up in the expr or column.

SELECT [tblParticipantData].* , ranges.ReturnThat
FROM [tblParticipantData], ranges
WHERE ( [tblParticipantData].BMI >= ranges.FromThis
AND [tblParticipantData].BMI < ranges.ToThis)
Click to expand...

I even tried it without the brackets around the table name.
BMI represents the field that I want compared to the "ranges table", I'd
like the value that equates to the range to showup in the expr column of my
table. When I run the query, it shows up as a parameter query, but my
understanding was that this would update the table with this value pulled
from the ranges table , in this case "1, 2 or 3". Is there another or
easier way to achive this same end (Ability to compare the value in the
existing table for BMI to a range, in this case in another table?)

Michel Walsh said:
In

SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1].Compare < ranges.ToThis)


I assume the table containing the BMI measurements is [Test Results Group
1]; I assume the field name that holds the value to be compare for the
ranges is name Compare. (It was missing for the comparison with the upper
limit of the range).


Note that if you modify the query to:

SELECT [Test Results Group 1].* , ranges.ReturnThat
FROM [Test Results Group 1], ranges
WHERE ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1].Compare < ranges.ToThis)



you should be able to get the query graphical query editor back.




Hoping it may help,
Vanderghast, Access MVP



Steelghost said:
I understand the ill naming could be part of the problem so I renamed in
the
correct format of tblMyTable. However, my disconnect at this point is,
after
the table has been created, How do I get the table containing the BMI
measurement to use this "ranges" table to compare the values? I have
tried
different methods and came up empty, Can you assist?

Thanks in advance!

Michel Walsh said:
Since your table name is ill formed, you have to use [ ] around it.


SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1]< ranges.ToThis)





Vanderghast, Access MVP


Hi Michel,

I tried this but can't get it going for the likes of me, I tried both
the
compare statement that you show below and the sql statement , Here's
what
I
tried...

SELECT Test Results Group 1.* ,ranges.ReturnThat
FROM Test Results Group 1 INNER JOIN ranges
ON ( Test Results Group 1.Compare >= ranges.FromThis
AND Test Results Group 1< ranges.ToThis)

My table that was created looks exactly like yours. What am I missing?
I
have created an inner join with the tables but had no luck, tried the
compare
statement with the queirs but had no luck going that router either, its
somewhat frustrating, Can you please assist and show me what I'm doing
wrong?
:

You can use range of values, in a table:


FromThis ToThis ReturnThat
0 20 1
20 32 2
32 40 3
.....



You can use:


compare >= FromThis AND compare < ToThis


as in:


SELECT yourtable.*, ranges.ReturnThat
FROM yourTable INNER JOIN ranges
ON ( yourTable.Compare >= ranges.FromThis
AND yourTable.Compare < ranges.ToThis )



Hoping it may help,
Vanderghast, Access MVP


Hi, I see. More specificially. I have created a Query that will
determine a
participant's BMI (Body Mass Index) and would like to know the best
way
of
using this query to determine what percentile the participant should
fall
into. It would also be helpful if I could somehow determine how to
break
this BMI down by age group since depending on the age group the mean
will
be
higher or lower. I'm not sure, if there is even a way to specify
the
expected curve or values by age group within Access but would like
to
know
if
anyone can assist me.

:

I need help creating a query in MS Access 2007 that will take a
determined
value (FC) and run a query against it showing what percentile the
value
should fall into based on person's age? Is there also a way to put
actual
parameters defining the expected curve within an access database to
be
applied toward the initial question.
Click to expand...
Click to expand...
Click to expand...
 
That is a SELECT query, it does not update any table. The query is making a
lookup over a range.


You use the query, instead of the original table, when you need the
lookup-value. It is much safer than updating a value in the a table, since
the query is always up to date, with any modification you could have made in
any of the two tables.


The proposed query works, as you can test the following in Northwind:

SELECT Orders.*, Ranges.ReturnThat
FROM Orders, Ranges
WHERE Orders.OrderDate>=[Ranges].[FromThis]
AND Orders.OrderDate<[ranges].[ToThis];



with


Ranges FromThis ToThis ReturnThat
1996.01.01 1996.10.31 aaaa
1996.10.31 1997.12.31 bbbb
1997.12.31 2999.12.31 cccc




which returns the third column of table Ranges, appropriately.



Vanderghast, Access MVP


Steel said:
I have tried everything but still can not get my exisiting tables to use
the
values that I expect to show up in the expr or column.

SELECT [tblParticipantData].* , ranges.ReturnThat
FROM [tblParticipantData], ranges
WHERE ( [tblParticipantData].BMI >= ranges.FromThis
AND [tblParticipantData].BMI < ranges.ToThis)
Click to expand...

I even tried it without the brackets around the table name.
BMI represents the field that I want compared to the "ranges table", I'd
like the value that equates to the range to showup in the expr column of
my
table. When I run the query, it shows up as a parameter query, but my
understanding was that this would update the table with this value pulled
from the ranges table , in this case "1, 2 or 3". Is there another or
easier way to achive this same end (Ability to compare the value in the
existing table for BMI to a range, in this case in another table?)

Michel Walsh said:
In

SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1].Compare < ranges.ToThis)


I assume the table containing the BMI measurements is [Test Results
Group
1]; I assume the field name that holds the value to be compare for the
ranges is name Compare. (It was missing for the comparison with the
upper
limit of the range).


Note that if you modify the query to:

SELECT [Test Results Group 1].* , ranges.ReturnThat
FROM [Test Results Group 1], ranges
WHERE ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1].Compare < ranges.ToThis)



you should be able to get the query graphical query editor back.




Hoping it may help,
Vanderghast, Access MVP



Steelghost said:
I understand the ill naming could be part of the problem so I renamed in
the
correct format of tblMyTable. However, my disconnect at this point is,
after
the table has been created, How do I get the table containing the BMI
measurement to use this "ranges" table to compare the values? I have
tried
different methods and came up empty, Can you assist?

Thanks in advance!

:

Since your table name is ill formed, you have to use [ ] around it.


SELECT [Test Results Group 1].* ,ranges.ReturnThat
FROM [Test Results Group 1] INNER JOIN ranges
ON ( [Test Results Group 1].Compare >= ranges.FromThis
AND [Test Results Group 1]< ranges.ToThis)





Vanderghast, Access MVP


Hi Michel,

I tried this but can't get it going for the likes of me, I tried
both
the
compare statement that you show below and the sql statement ,
Here's
what
I
tried...

SELECT Test Results Group 1.* ,ranges.ReturnThat
FROM Test Results Group 1 INNER JOIN ranges
ON ( Test Results Group 1.Compare >= ranges.FromThis
AND Test Results Group 1< ranges.ToThis)

My table that was created looks exactly like yours. What am I
missing?
I
have created an inner join with the tables but had no luck, tried
the
compare
statement with the queirs but had no luck going that router either,
its
somewhat frustrating, Can you please assist and show me what I'm
doing
wrong?
:

You can use range of values, in a table:


FromThis ToThis ReturnThat
0 20 1
20 32 2
32 40 3
.....



You can use:


compare >= FromThis AND compare < ToThis


as in:


SELECT yourtable.*, ranges.ReturnThat
FROM yourTable INNER JOIN ranges
ON ( yourTable.Compare >= ranges.FromThis
AND yourTable.Compare < ranges.ToThis )



Hoping it may help,
Vanderghast, Access MVP


message
Hi, I see. More specificially. I have created a Query that will
determine a
participant's BMI (Body Mass Index) and would like to know the
best
way
of
using this query to determine what percentile the participant
should
fall
into. It would also be helpful if I could somehow determine how
to
break
this BMI down by age group since depending on the age group the
mean
will
be
higher or lower. I'm not sure, if there is even a way to specify
the
expected curve or values by age group within Access but would
like
to
know
if
anyone can assist me.

:

I need help creating a query in MS Access 2007 that will take a
determined
value (FC) and run a query against it showing what percentile
the
value
should fall into based on person's age? Is there also a way to
put
actual
parameters defining the expected curve within an access database
to
be
applied toward the initial question.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top