Difficult query?

[George]

Dear friends,

I have a database in Access 2003 about Forest Inventory. I have data
collected from sample plots in a table (T_RawData1991-2001 – linked from
T_SamplePlotsDetails one to many). For each tree I have its SerialNo
recorded manually, so in a sample plot I may have 5 trees (up to 100),
serially 1-5. I have also recorded the distance and the azimuth of each tree
from the center of the sample plot.

Now, I want to have (using a query perhaps) the following results:
The distance combination between all Trees e.g. 1-2, 1-3, 1-4, 1-5, 2-1,
2-3, 2-4, 2-5, 3-1, 3-2, 3-4, 3-5, 4-1, 4-2, 4-3, 4-5, 5-1, 5-2, 5-3, 5-4.

Taking into consideration the first distance (between tree 1 and 2):

Distance Between tree 1 and 2: length1 + length2
Length1: sin1 * L1
sin1 is the sin of the azimuth of the first tree and L1 is the distance of
the first tree
Length2: sin2 * L2
Sin2 is the sin of the azimuth of the second tree and L2 is the distance of
the second tree

Is it possible?

Thanking you so much in advance,

GeorgeC

 

[scubadiver]

Dont forget the distance between 1-3 is the same as between 3-1 ;-)

Apart from that what you are after isn't too clear (to me at least)

 

[Dale Fye]

George,

You just need to convert your polar coordinate system (azimuth and distance)
to X,Y coordinates, then use the formula D = SQRT((X1-X2) ^2 + (Y1-Y2)^2) to
get the distance between the various trees.

To do this in a Query, I would first write a function that I pass the values
of the azimuth and distance to two trees (I'll call it fnDistance, and leave
it up to you to come up with what goes in the function). Then, I would write
a query that looks something like:

Select T1.Plot, T1.SerialNo, T2.SerialNo,
fnDistance(T1.Azimuth, T1.Distance, T2.Azimuth, T2.Distance) as
Separation
FROM yourTable T1
INNER JOIN yourTable T2
ON T1.Plot = T2.Plot
WHERE T2.SerialNo > T1.SerialNo

HTH
Dale

 

[George]

Thanks a lot for your prompt answer.

Sorry but my knowledge in VBA is very poor?

Can you explain a bit further?

Thanks in adnvance,

GeorgeC


Ο χÏήστης "Dale Fye" έγγÏαψε: