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

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.  
0
Utf
2/1/2008 9:40:54 PM
access.queries 6343 articles. 1 followers. Follow

12 Replies
1090 Views

Similar Articles

[PageSpeed] 1

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


"Steelghost" wrote:

> 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.  
0
Utf
2/4/2008 4:19:00 AM
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.

"Steelghost" wrote:

> 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.  
0
Utf
2/4/2008 6:35:03 PM
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" <Steelghost@discussions.microsoft.com> wrote in message 
news:FEA574EE-1F3B-47F3-859E-4F2CBA45FB0B@microsoft.com...
>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. 


0
Michel
2/4/2008 6:52:48 PM
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


"Steelghost" <Steelghost@discussions.microsoft.com> wrote in message 
news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
> 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.
>
> "Steelghost" wrote:
>
>> 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. 


0
Michel
2/4/2008 6:56:49 PM
Thanks Michel,

I'll give your suggestions a try and let you know how things turn out.
"Michel Walsh" wrote:

> 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" <Steelghost@discussions.microsoft.com> wrote in message 
> news:FEA574EE-1F3B-47F3-859E-4F2CBA45FB0B@microsoft.com...
> >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. 
> 
> 
> 
0
Utf
2/4/2008 7:47:02 PM
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?
"Michel Walsh" wrote:

> 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
> 
> 
> "Steelghost" <Steelghost@discussions.microsoft.com> wrote in message 
> news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
> > 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.
> >
> > "Steelghost" wrote:
> >
> >> 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. 
> 
> 
> 
0
Utf
2/5/2008 7:21:01 PM
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" <Steelghost@discussions.microsoft.com> wrote in message 
news:C1A38563-F92D-4012-AF70-C4CA9FD20720@microsoft.com...
> 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?
> "Michel Walsh" wrote:
>
>> 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
>>
>>
>> "Steelghost" <Steelghost@discussions.microsoft.com> wrote in message
>> news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
>> > 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.
>> >
>> > "Steelghost" wrote:
>> >
>> >> 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.
>>
>>
>> 


0
Michel
2/5/2008 8:00:46 PM
Thanks Michel, I tried this and it worked.

"Michel Walsh" wrote:

> 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" <Steelghost@discussions.microsoft.com> wrote in message 
> news:FEA574EE-1F3B-47F3-859E-4F2CBA45FB0B@microsoft.com...
> >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. 
> 
> 
> 
0
Utf
2/6/2008 5:40:00 PM
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" wrote:

> 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" <Steelghost@discussions.microsoft.com> wrote in message 
> news:C1A38563-F92D-4012-AF70-C4CA9FD20720@microsoft.com...
> > 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?
> > "Michel Walsh" wrote:
> >
> >> 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
> >>
> >>
> >> "Steelghost" <Steelghost@discussions.microsoft.com> wrote in message
> >> news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
> >> > 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.
> >> >
> >> > "Steelghost" wrote:
> >> >
> >> >> 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.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/6/2008 5:44:02 PM
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" <Steelghost@discussions.microsoft.com> wrote in message 
news:CA5F3741-4699-475F-9F88-EEF7C16D2221@microsoft.com...
>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" wrote:
>
>> 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" <Steelghost@discussions.microsoft.com> wrote in message
>> news:C1A38563-F92D-4012-AF70-C4CA9FD20720@microsoft.com...
>> > 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?
>> > "Michel Walsh" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Steelghost" <Steelghost@discussions.microsoft.com> wrote in message
>> >> news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
>> >> > 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.
>> >> >
>> >> > "Steelghost" wrote:
>> >> >
>> >> >> 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.
>> >>
>> >>
>> >>
>>
>>
>> 


0
Michel
2/6/2008 6:03:20 PM
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)

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" wrote:

> 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" <Steelghost@discussions.microsoft.com> wrote in message 
> news:CA5F3741-4699-475F-9F88-EEF7C16D2221@microsoft.com...
> >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" wrote:
> >
> >> 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" <Steelghost@discussions.microsoft.com> wrote in message
> >> news:C1A38563-F92D-4012-AF70-C4CA9FD20720@microsoft.com...
> >> > 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?
> >> > "Michel Walsh" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "Steelghost" <Steelghost@discussions.microsoft.com> wrote in message
> >> >> news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
> >> >> > 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.
> >> >> >
> >> >> > "Steelghost" wrote:
> >> >> >
> >> >> >> 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/11/2008 8:33:12 PM
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" <Steel@discussions.microsoft.com> wrote in message 
news:D474C950-1EA7-4832-AD90-3F424CC5A97E@microsoft.com...
>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)
>
> 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" wrote:
>
>> 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" <Steelghost@discussions.microsoft.com> wrote in message
>> news:CA5F3741-4699-475F-9F88-EEF7C16D2221@microsoft.com...
>> >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" wrote:
>> >
>> >> 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" <Steelghost@discussions.microsoft.com> wrote in message
>> >> news:C1A38563-F92D-4012-AF70-C4CA9FD20720@microsoft.com...
>> >> > 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?
>> >> > "Michel Walsh" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Steelghost" <Steelghost@discussions.microsoft.com> wrote in 
>> >> >> message
>> >> >> news:32EAD469-BACF-4793-992A-7F328661462D@microsoft.com...
>> >> >> > 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.
>> >> >> >
>> >> >> > "Steelghost" wrote:
>> >> >> >
>> >> >> >> 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Michel
2/11/2008 9:12:09 PM
Reply:

Similar Artilces:

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

LDAP Write access?
My ldap server allows Write access to entries - and a few clients now support this. Any plugins available for Outlook to allow this too? Thanks None that I'm aware of. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http://www.outlook...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Function to determine Directory Size
I'd like to be able to determine the size of a directory or group of files. Does anyone know of an add-in that has a function whereby I can specify a path and get the size of directory of groups of files as the return value? Ex: =dirsize("C:\Documents and Settings\Bob\My Documents\School") =dirsize("C:\Documents and Settings\Bob\My Documents\School\*.doc") It would also be helpful if there were an option to include subdirectories. -- Thanks, Bob Chmara Argh.... Make that "get the size of a directory or group of files" Bob "Bob Chmara" fatfing...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

How to place a stable title row?
Hi there! For a scientific analyse I need to create an Excel table with more than 1.000 rows content. Now, how can I create a title row that doesn't scroll with the rest of the content, so one that is always visible at the top, while you scroll up and down the rest of the content. I hope that's possible like this! Otherwise, it will be pretty difficult to enter the data into the correct (of about 15) column. Thanks for your help in advance! One way: Assuming headers are in row1, Select A2. Click Window > Freeze pane This freezes the 1st row, so it always remains in view w...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

Combo Box initial values question
Does anyone know how to make a combo box show a value when a sheet opens? Mine are always blank when I open them until I select a value. thanks tp Hi Teepee, Try something like: Me.ComboBox1.ListIndex = 0 --- Regards, Norman "teepee" <teepee@noemail.com> wrote in message news:4645ed29$1@newsgate.x-privat.org... > Does anyone know how to make a combo box show a value when a sheet opens? > Mine are always blank when I open them until I select a value. > > thanks > > tp > > thanks for trying. says 'invalid use of me keyword.&...