Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    3

    Unanswered: Calculations down columns

    Hi,

    I'm afraid that I'm quite new to all of this, so I'm sorry if this is an obvious question to ask...

    I'm trying to carrying out calculations down columns - I need to find the difference between a value in a column and the value above it in the same column. I have data on bird movement patterns and each row entry is a new position for the bird. I want to know how far the bird has travelled between the two positions.

    This is really just the start of the problem because I actually have two fields, an X and a Y coordinate for each position, so to calculate the distance travelled I'll need to do some trigonometry, which I'm also not sure how to do in Access.

    However, if anyone can just help me with the first part, I'd be really grateful!

    Thank you!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @ the Trigonometry problem:
    Sin() Cos() & Tan() are all VB functions

    Actually, if you know the co-ordinates, to work out the length of the diagonal between them you use Pythagoras'.

    (((Difference between X co-ordinates) ^ 2) * ((Difference between Y co-ordinates) ^ 2 )) ^ 0.5

    ( a^2 + b^2) ^ 0.5

    (Note: ^ 0.5 = square root )
    Last edited by gvee; 02-22-07 at 10:05.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Hypotenuse = ( a^2 + b^2) ^ 0.5

    ??

    MTB

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies - have edited last post to suit
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This requires a nested query, where your first query determines the position of the bird for a given date:

    Code:
    SELECT POS_DATE, BIRD_NUM, POS_X, POS_Y
    FROM tblBIRDS 
    WHERE POS_DATE=[yourdatecriteria] AND BIRD_NUM=[yourbirdcriteria]
    That query then gets "nested" (no pun intended) into another query
    that determines the next recorded date for that bird.
    So your final query would look like this:

    Code:
    SELECT POS1.BIRD_NUM, POS1.POS_DATE AS POS1_DATE, POS1.POS_X AS POS1_X, POS1.POS_Y AS POS1_Y
    , FIRST(POS2.POS_DATE) AS POS2_DATE, FIRST(POS2.POS_X) AS POS2_X, FIRST(POS2.POS_Y) AS POS2_Y
    FROM (SELECT POS_DATE, BIRD_NUM, POS_X, POS_Y
               FROM tblBIRDS 
               WHERE POS_DATE=[yourdatecriteria] AND BIRD_NUM=[yourbirdcriteria]) POS1
    INNER JOIN tblBirds POS2 ON POS1.BIRD_NUM=POS2.BIRD_NUM AND POS1.POS_DATE<POS2.POS_DATE
    GROUP BY POS1.BIRD_NUM, POS1.POS_DATE, POS1.POS_X, POS1.POS_Y
    I'll leave it to the math whizzes to help you from there.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In your select statement in the non-nested bit
    *takes a moment to laugh at:*
    Quote Originally Posted by RedNeckGeek
    That query then gets "nested" (no pun intended) into another query
    Code:
    SELECT ... , (((POS1.POS_X ^ 2) + (POS1.POS_Y ^ 2) ^ 0.5)) AS 'Flight'
    Please not that this is completely untested (therefore probably doesn't work) ;P
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2007
    Posts
    3
    Hi,

    Thanks for the replies - I'd forgotten that you could use ^0.5 in place of square root.

    The part that I'm actually struggling with is getting Access to work out the difference in the X and Y coordinates, because the successive positions are on different lines. I've exported a small amount of the data to an excel spreadsheet to demonstrate what I'm trying to do (which would be easy if I could use excel - I have far too many datapoints for excel). I only seem to be able to get Access to carry out calculations on data that are in the same row.

    Thanks again

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Gash, forgot these were stored in different rows.

    I'm thinking; alias the subquery and then use that to calculate the difference between Xs and Ys?

    Anyone?
    Last edited by gvee; 02-22-07 at 11:22.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2007
    Posts
    3
    Sorry - I'd replied before I saw the other responses above.

Posting Permissions

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