1. Registered User
Join Date
Feb 2007
Posts
3

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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
@ 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 09:05.

3. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
Hi

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

??

MTB

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Apologies - have edited last post to suit

5. Village Idiot
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.

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
In your select statement in the non-nested bit
*takes a moment to laugh at:*
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

7. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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 10:22.

9. Registered User
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
•