Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: Greater Than equation??

    Hi

    In my database i have a different table for each appointment that a client ttends. There are 5 appointments and the same measurements are taken at each appointment.

    Activity Levels is one of the measurements taken at each appointment. I want to be able to have a query that finds the people whose activity levels have increased between the first and last appointment. Is this possible.


    Cheers

  2. #2
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24
    You should be able to do it,

    Question: How are you recording level of activity (is it numerical ie scale 1- 10)

    you could try

    SELECT me.someactivity
    FROM me.activitytable
    WHERE activity > (SELECT avg(someactivity) FROM activitytable);


    try that (as sn SQL statement either on its own or in MS Access SQL view) or if your using MS Access (in query design view) under the criteria put =>

    think that should help (maybe not)

    substitute your table and filed names for the 'someactitvity' and 'activitytable'

    HTH

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    noddy is on the right track I think.. If you want to use ONLY the first and last, you could alter the statement a bit:

    SELECT me.someactivity
    FROM me.activitytable
    WHERE activity > (SELECT TOP 1 someactivity FROM activitytable WHERE activitytable.clientid=me.clientid ORDER BY date ASC)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2004
    Location
    Scotland
    Posts
    24
    been thinking about this all day.....actually a better way of doing this sort of query would be to use the function max()

    your query would now look like:

    SELECT some_ability_score
    FROM sometable
    WHERE some_ability_score > (SELECT max(some_ability_score)
    FROM sometable ); [and if you need to join tables
    use
    WHERE sometable.some_ability_score =
    anothertable.some_ability_score ]

    this takes the existing max value and checks to see if the value you enter is greter than it!

    Should be more what your looking for!

    Cam

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just being nosey at this point, but why on earth would you have five identical tables ?!?! Wouldn't this be faster and easier if you had everything in one table?

    I would expect that you'd create a query that joined the two tables based on the appropriate key values, then compared the two columns.

    Am I missing something?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •