Difficult query?

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

0
Utf
1/14/2008 9:28:02 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
271 Views

Similar Articles

[PageSpeed] 26

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)

"George" wrote:

> 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
> 
0
Utf
1/14/2008 11:24:02 AM
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
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"George" wrote:

> 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
> 
0
Utf
1/14/2008 12:51:01 PM
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" έγγραψε:

> 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
> -- 
> Don''t forget to rate the post if it was helpful!
> 
> email address is invalid
> Please reply to newsgroup only.
> 
> 
> 
> "George" wrote:
> 
> > 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
> > 
0
Utf
1/15/2008 8:36:01 AM
Reply:

Similar Artilces:

Display table name as field in query
Does anyone know if there is a way to display the table name as a field in a query? I have a UNION query that merges all of the records from 10 different tables, but I need a field that indicates which table each record comes from. Any help would be greatly appreciated. Thanks, Craig Craig wrote: >Does anyone know if there is a way to display the table name as a field in a >query? > >I have a UNION query that merges all of the records from 10 different >tables, but I need a field that indicates which table each record comes from. Just use the table name in a calcula...

queries -- outer join
Hi, I want to know if is possible make an OUTER JOIN using more than two tables, because when I try appear a message that says is wrong thanks in advance loparqu -- Message posted from http://www.ExcelForum.com No you can't. and you're in the wrong newsgroup. >-----Original Message----- >Hi, I want to know if is possible make an OUTER JOIN using >more than two tables, because when I try appear a message that >says is wrong >thanks in advance > >loparque > > >--- >Message posted from http://www.ExcelForum.com/ > >. > Hi you probably sh...

Union Query and Where Clause
Is it is possible to apply WHERE clause to the entire result of the UNION query? Something like this: (Select f1 from A UNION Select f1 from B) where f1 = '1'; I tried to parenthesize the individual selects but it does not work. So I had to put the where clause in each individual select to filter the result. Thanks. Not within the UNION query itself. You'd need to save the UNION query and then write a different query that uses that UNION query as the data source, and then put the WHERE clause in that new query: SELECT * FROM UNIONQuery WHERE FieldName = "value"; ...

Looping through Query to create multiple sheets in excel- Just need the loop
I figured out where I should start the loop in order to keep the excel work open and still be able to add more sheets, but I can't figure out how to add code to For Next loop to go through a query "qryManufacturer" and take each one and put them into the string (strManuf) I always get to this point and I can't figure out how to loop through a recordset. I have put the string in the query at the bottom. Public Sub CopyRs2SheetHacked(strSql As String, strWorkBook As String, _ Optional strWorkSheet As String, Optional strRange As String) 'Uses the Excel...

Query error
I have a set of queries that load data from an excel spreadsheet. Lately I have been getting an error that states "Field 'F18' doesn't exist in destination table 'tblTempProduction.' I have checked and this field does exist in this table. The strange thing about it is I do not receive this error everytime I run the series of queries. It just happens randomly. Any suggestions? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 ...

can't get query to group
here is the sql. can someone help me determine why the query isn't grouping by GoupNumber? The GroupName is the same for the GroupNumber. ID is unique. SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName FROM Accounts GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID HAVING (((Accounts.ID) Like "01*")); Example ID GroupNumber GroupName 010101 01 red 010102 01 red 010201 02 blue 010301 03 ...

Too difficult!
I don't seem to be able to get an answer to this question - is it so difficult or am I in the wrong forum? How can I change format a column in all 30 or so sub- folders in my INBOX in one go i.e. without doing them individually. Thanks for any help. Glyn Create your own view in View-> Arrange By-> Current View-> Define Views... and apply it on first use of the folder -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Glyn" <anonymous@d...

Difficult function: help needed.
In need a function which calculates an overall weighing factor based on person age and the years the person is working at a company. For each working year factor 1. For all working years while older then 41 an addtional factor 0.5. For all working years while older then 51 an additional factor 0.5. Thanks for any help. To be able to use this function easily in each case paste this command into a Visual Basic Project, Macro Module: Function Factor(Years) If Years > 51 Then Factor = 2 ElseIf Years > 41 Then Factor = 1.5 Else Factor = 1 End If End Function Therafter you wil...

Can Pivot Table views of a query be saved as a favorite?
....if so, how? Yes. Have the query properties sheet visible (right click on an empty spot on the top half, if not, or use the toolbar): the second property is Default View. Specify the one you wish to get. Hoping it may help, Vanderghast, Access MVP <soarathorn@gmail.com> wrote in message news:1177084713.529654.35070@l77g2000hsb.googlegroups.com... > ...if so, how? > ...

Difficult but do-able?
In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** If on sheet1 you have these columns: A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(S...

linking two tables in query
I'm trying to link two tables using an ODBC table in query. Not all records in the left table have an entry in the right table. I've tried all combinations of the link properties but I either get only those records that have entries in both tables or each record in the left table duplicated by the number of records in the right table. Any advice gratefully received --- Message posted from http://www.ExcelForum.com/ Hi there You need to create the link in Query by dragging one field onto the other, then double-click on the link line. You'll either want option 2 or option ...

Excel 2003/MS Query
Hi I have several spreadhseets set up with sql queries to our financial system. From time to time I am having issues with the query definition being lost (almost like someone has pasted values - but they haven't) (Save query defn is ticked under Data Range Properties) This is driving me mad - I can't find anyone that seems to have come across this problem Someone please help ! ...

DNS return queries through isa2000 for exchange
I recently stared having a problem where our email is being returned without delivery from comcast email servers. The return message is the following: <mail.XXXXXX.com #5.5.0 smtp;521-EHLO/HELO from sender ###.###.###.### does not map to mail.XXXXXX.com in DNS> (### is the ip address of the isa server) I'm guessing that they recently started requiring a matching DNS reply before accepting incoming email. I haven't changed anything on my end so that is why I assume the change came from them. My exchange server is on the inside (NATed) of the network. The IP address we have...

Inverting a query
Hey folks, it got to build a database for a company archive. It cotains some queries. The last query I got to do shall show the unused racks. If using the following query: SELECT zuordnungfa.archiv, zuordnungfa.schrank AS zuordnungfa_schrank, zuordnungfa.seite, zuordnungfa.fa, archiv.schrank AS archiv_schrank FROM zuordnungfa INNER JOIN archiv ON zuordnungfa.schrank = archiv.schrank; the query returns every rack, containing one or more folders. This is exactly the opposite of what I want. Is there any way to select the inverse or should I write a new (better) query, and how? Here are the t...

How to query when control param is null?
I want to run a query with criteria based on a form control. Works fine for nonblank values in the control but when the control is null the query finds no records, even though records do exist with null values in that field. Does anybody know how I can fix this? You cannot use a parameter to find Null. By its nature, Null is not equal to Null (i.e.: Null = Null will never return True) If the parameter is Null, do you want only Null values returned, or do you want all the rows returned? If the former, your SQL should be something like WHERE MyField = Forms!MyForm!MyControl OR (MyF...

Why is it so difficult???????
I posted an issue yesterday from my home computer. Today at work (WHERE I NEED THE ANSWER!!!!!!) I can't find the post from yesterday! I've looked and looked and looked but I can't find it. Searching for the subject doesn't find it. HOW do I search for posts by me? My signature doesn't work, my email doesn't work, etc. This is EXTREMELY FRUSTRATING!!!!!!! If you posted to this newsgroup using this alias I can't find the message. It was either deleted for some reason, you posted to a different newsgroup, or used a different user. patrick developer support --...

Very difficult query (?)
Hello I've been struggling with this all day, and although I think I'm near a solution (actually I thought that 2 hours ago!) it now involves 4 nested queries and is 'grinding' horribly (i.e. takes 30-40 seconds to run), and I'm convinced that there must be a better way to get what I need! I really hope someone can help with this. I have a table [x confirmed] which includes the following fields: 'employeename' (text) 'month number' (integer) 'cont rate' (number - percent) 'nhsp ees' (number - currency) 'pen pay' (numb...

Excel query
Hello I am trying to add a drop-down list to a cell with info like different brands of smoke detectors and the corresponding prices. When I select a brand the list disappears and leaves me with the brand and price in that cell. Is this possible? Judy Hi On empty sheet, enter the table (starting from A1) Brand1 Price1 Brand2 Price2 .... Let's assume your table fills the range A1:B20 Rename the sheet p.e. as "PriceTable" Select from menu Insert.Name.Define Into name field enter some name, p.e. PriceList Into source field, enter the formula: =OFFSET($A$1,,,COUNTIF($A...

Query Criteria IIf statement
I'm using MSAccess 2003 and this seems like a simple process, especially compared to some of the things I'm trying to do in my database. In the criteria of a query field, I am using the following IIf statement: IIf([FYAGR]>3000000,3,<4) [FYAGR] is a different field in the same query. When [FYAGR] is greater than 3,000,000, I get 3 as expected, but no matter how I word the range of numbers I'm looking for in the false part of the statement, my query returns no records. If I remove the IIf statement and place '<4' I get the correct range, but no ...

Can't enter data in query datasheet
I have a query (involving multiple tables) that I want to execute and then use to enter/change data via the datasheet which results from the query. On most queries, I am able to do that. One this one, I can't. What is the criteria for determining what queries will allow data entry and which queries won't? What do I have to do to this query to allow data entry? SELECT [4GateMeetingTimes].Time, ActivityTbl.Activity_ActivityKey, ActivityTbl.Activity_Deployment, ActivityTbl.Activity_Name, ActivityTbl.Activity_Status, ActivityTbl.Activity_Type, ActivityTbl.Activity_Pl...

Show only Latest date in reuslts of a query
Hi I have a parent table holding review dates and a child table with audit trail entries made with dates. I want to extract all documents with a review date within a range (select query doing this) and would also like to see only the latest audit trail entry in the the child table. At the moment my select query shows a document with all audit trail entries. Any suggestions on how I can get only the latest audit trail entry would be deeply appreciated. Thanks Dee Select query left join to --- Group by query with these fields -- Audit_Identifier Max(audit trail entry da...

difficult format
I want to format a code like this: ##.0001.## so then i push it down and gives me 0002 and so on, the other ## r numbers. The problem is that when i do that, it sums in the last two numbers instead of what i want, so i ask if it is possible to format like this: (text)##.(number)0001.(text)## so it guives me the sum of number then do what i want. if it is possible how ca i do that? Thanks You can try this, starting in row 1: ="##."&TEXT(ROW(),"0000")&".##" You'll have to adjust the "Row()" number when starting in any other row. For example,...

queries #2
I have a co-worker who wants to be able to do a query within a worksheet - it should be ridiculosly easy, but I can't seem to figure it out, and Excel 2003's help only speaks of importing queried data into Excel, and that's not what I want. help?? "Dave" <Dave@discussions.microsoft.com> wrote ... > I have a co-worker who wants to be able to do a query within a worksheet - it > should be ridiculosly easy, but I can't seem to figure it out, and Excel > 2003's help only speaks of importing queried data into Excel, and that's not > what...

Display Query from form in Foreground
I have a form upon which the user selects display, filter and sort fields. He then clics the submit button and a query is built and then displayed with the following code: DoCmd.OpenQuery "Queryname", acNormal, acEdit The query works but shows up in the background and cant be clicked on until the form is closed. The idea was to create a form on which the user could do adhoc queries until he gets it right and then click another button to do the Excel extract. Is there a way to show the query in the foreground? On Tue, 22 Jan 2008 17:54:27 -0800, rmcompute wrote: > I ha...

Dilemma with form bound to a query....
How can I enter data into my tables through a form bound to a query? There must be a way but it eludes me if there is. I am fairly new at this so lots of things baffle me at this point. Please help. Pat -- "If you can find a path with no obstacles, it probably doesn't lead anywhere" "Dupatt" <Dupatt@yahoo.com> wrote in message news:eImdnc0oa8yIys_bnZ2dnUVZ_qWvnZ2d@comcast.com... > How can I enter data into my tables through a form bound to a query? > There must be a way but it eludes me if there is. I am fairly new at > this so ...