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
516 Views

Similar Articles

[PageSpeed] 54

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:

difficult formula
How to explain this. In a range of cells, row J4:AB4 I will be entering numbers weekly, eventually filling up to AB4. When I enter a number into, lets say, O4, I want a calculation of the average of ONLY L4, M4, N4 Next week, when I enter a number into P4, I want a calculation of the average of ONLY M4, N4, O4 So the result, in the cell that the formula is in, is changing weekly. I hope this is clear. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com One way =AVERAGE(OFFSET($J$4,,COUNT($J$4:$AB$4)-2,,-3)) assuming that there are numbers and they will ...

Hide duplicates in a query ...
Hi folks I would like to hide any duplicate records in a query. I have already used the "Unique Values"parameter, but I would like to hide any records where the first field is duplicated. Did that make any sense?? ... eg. Field 1 Field 2 1 a 1 a 2 b 2 c I would only like to show one record for "1" & one for "2" etc ..... Can anyone help? Thanks If it makes no difference which record of the group you want to see, you could use an aggregate query where you group by field 1 and specify firs...

Union Query
A union query I developed has suddenly stopped working. The syntax is: SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part 2]; Pulling up each query individually works fine. When i run the above union query I receive "Invalid use of Null". The number and order of the columns in each query are identical. Access 2003 here. Any ideas what could be the problem? I suspect it is the VBA code that you are using around the query, not the query, that stopped working. To confirm, does your query is executable from the query editor? If it does not, you also confirm t...

open saved query in Excel 2003
I have many queries I created in Excel 2000. In 2003, there is no option to open a saved query. Is this a new feature of 2003 or does someone know how to open my older queries? TIA, Joe Data, Import External Data, Import Data will let you open/run any saved queries (IQY files). -- Jim Rech Excel MVP "jaylou" <jaylou@discussions.microsoft.com> wrote in message news:31060409-532E-470D-A0AF-0E52BF383E8B@microsoft.com... |I have many queries I created in Excel 2000. In 2003, there is no option to | open a saved query. Is this a new feature of 2003 or does someone know...

Ldap Query
I want to make a query where i can see the members of an administrative group or Distribution list. Is there someone who has such a query? administrative group would be based on something similar to (legacyexhangedn="/o=Microsoft/ou=First Administrative Group/cn=Recipients") dl would be based on (memberOf="DN of Group") This works fine as long as the group isn't the users primary group -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this...

Including Null values in query design
I am using a form to enter the parameters for a report. The parameters are drawn from a number of combo boxes on the form. The report is based on a query. Several of the query fields have expressions such as: Like IIf(IsNull([forms]![frmReports]![cboIssuedBy]),"*",([forms]! [frmReports]![cboIssuedBy])). This works fine as long as the fields that are being queried have no null values - if they do, then in the situation where I want the "*" to apply, the query only returns the records which have a value. Really, I want to be able to replace the "*" pa...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

Countif query
Take 2 - I don't think my first request got sent. I am attempting to count the number of cells in AB2:AB26 that have a number less thn zero. I have attempted this formula without success: =COUNTIF(AB2:AB26,<0) =COUNTIF(AB2:AB26,"<"&0) -- Max Singapore --- "Keith" wrote: > Take 2 - I don't think my first request got sent. > > I am attempting to count the number of cells in AB2:AB26 that have a number > less thn zero. I have attempted this formula without success: > > =COUNTIF(AB2:AB26,<0) > Hi, ...

Queries #3
Can an Access append query be run from an excel spreadsheet? If so, how? ...

Excel Query Error
I have an Excel template that contains a query. This information goes back to an Access database. There are three worksheets with three queries in this template. It would have been my choice to keep it all contained in Access, but I didn't design it. When this template is opened it automatically refreshes the information. However, on one computer we receive an error that says the ODBC is not connected no driver manager. We have verified there is a full install of Office on that pc. I don't know how to fix this so she can run this from her pc. The query is not complex ...

Supporting international phone numbers shouldn't be so difficult.
Why can't Great Plains have a pulldown for the country on any address cards that then changes the address and phone number formats accordingly? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser...

Building an expression from a query
I am a self learned Access07 user. I have created a database and need some experience user knowledge. In a form, i would like once information is enter in to one text box another text box automatically updates according to a query. I worked with someone with a little more knowledge then I, but they were unsure of how to complete it. Pls help Put the following code in the AfterUpdate event of the first textbox: Me!NameOfOtherTextBox.Requery This assumes you have a calculation in the cntrol source of the other textbox. In the alternative your code could calculate the ...

MS Access Duplicates Query
While cleaning data recently, I stumbled accross this built-in query in Access 2003. Many of you may already know of this tool, if not give it a try as it is very simple to use. You can quickly locate and remove duplicates in your source data. Start by Importing your data into Access tables. Then go to Queries in the Objects pane. On the toolbar above, click the New button and select the 'Find Duplictes Query Wizard. You can then delete bad records here or export to Excel if you need to have your data validated or reviewed by your customer. ...

Update Query does not clear all records immediately
Hi All, I have a form on which there are several "Flag" text fields which users use to mark records. Next to each flag field there is a clear button that runs the following code: Private Sub btn_ClSA1Flag1_Click() On Error GoTo Err_btn_ClSA1Flag1_Click Dim stDocName As String stDocName = "QryUpd_ClearSA1Flag1" DoCmd.OpenQuery stDocName, acNormal, acEdit Me.Requery 'DoCmd.OpenQuery stDocName, acNormal, acEdit Exit_btn_ClSA1Flag1_Click: Exit Sub Err_btn_ClSA1Flag1_Click: msgbox Err.Description Resume Exit_btn_ClSA1Flag1_Click ...

Database query, spreadsheet size
I have a spreadsheet with several database queries from SQL Server. As queries are refreshed, spreadsheet size continues to grow. Even when I wipe out the queries, the file stays around 300K. Does anyone know where this extra data is sitting and how can I clear it out. Hi 300K is not that big but have a look at: http://www.contextures.com/xlfaqApp.html#Unused >-----Original Message----- >I have a spreadsheet with several database queries from >SQL Server. As queries are refreshed, spreadsheet size >continues to grow. Even when I wipe out the queries, the >file stay...

Access form date query
Hello all, I have a form in which I have 2 combo boxes. 1 called StartMonth and another called EndMonth. I have a query called MonthsAvailable, this gets its info from Date Out By Month: Format$(JobsInv.DateOut,'yyyy mm') The combo boxes fill ok with data like 2008 06 and so on no problems. My problem is that I want to capture all data from StartMonth and EndMonth, eg 1/6/2006 through to 31/8/2007. I have this criteria set up in a report: >=[Forms]![DataDrill]![StartMonth] And <=[Forms]![DataDrill]![EndMonth] for some reason the data retrieved only read from the 1st of e...

fail to create IXSSO.Query object
I'm moving classic asp sites from Windows 2000 / IIS 5 to Windows 2008 R2 / IIS 7. We have the "Indexing Service" started. Catalogs were created OK. However, I am unable to create an ixsso.Query object. Get the following error message Microsoft VBScript runtime error '800a01ad' ActiveX component can't create object /HC/ECO/Announcements/Headlines/SearchResults.asp, line 39 ASP code : <% Dim sSearchString Dim oQuery Dim results() ...

Form Combo Box Causes Query Error Message
Using Access 2007, I created two similar combo boxes, using the control wizard for each form, in design view, one for each of two separate databases. For one database it presents no problem at all. But for the other, when I create a query, initially the query displays during the creation process. However, every time I try to re-open the query, it re-opens and displays the correct data, BUT only after I close an error message box that always pops up. The error message says: "An invalid condition was encountered by Access due to a value that you entered for a field or table prope...

The Indexing Service query cannot be completed successfully
I'm trying to do a search for *.txt files containing a certain phrase and am unable to do so. When I try I get the following message: Search Companion The Indexing Service query cannot be completed successfully because the volumes you have specified are not indexed. I can do searches for *.txt files containing phrases in other directories - just not this particular one. How can I reenable it? R-click the Folder, select Properties, Advanced... yawnmoth wrote: > I'm trying to do a search for *.txt files containing a certain phrase > and am unable to do so. W...

query criteria 12-29-07
I have a criteria test in the [DR].[Received Date] field of a query. The criteria is based on data which is entered into a form. >=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate])) The true side of the IIF allows for all received dates if no data is entered into the form. I would like to instead not do a criteria test at all if there is no date entered into the form. I've tried things like "" or NULL in the criteria, but to no avail. The reason for wanting to do this, is that I have two tables linke...

Freeze panes query
Is it possible to freeze panes without a line being drawn right accross the whole page? Nope. But maybe you coul apply a bottom border (nice thick red border) so that it looks like you did it on purpose. PigzleFly wrote: > > Is it possible to freeze panes without a line being drawn right accross the > whole page? -- Dave Peterson ...

Relationship error in a query
Hello Everybody, I have four tables in a databse. Each database has at least one column containing prices of goods & services. However, these columns are not related in anyway (some other colums are). I am trying to summerize these price information in one query where I am trying to list the sum of prices from every table. When I try to do so I am getting an error part of which says: "One or more of the tables isn't related to the others. Click OK to edit system relationships." At least according to the error message (and this would have been my question anyway),...

Query table using criteria from an Excel document
Hello, I have a Access table of names and addresses. There is a postcode (zipcode) text field. I also have an excel spreadsheet with a single column of postcodes; no duplicates. I need to get query to return each name and address in the table which has a postcode listed in the spreadsheet. Typing each postcode manually into the query seems clumsy. Any ideas? Add a link to the Excel data. Create a new query joining the linked Excel data and your Access table on the postcode field. Those that match will result. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/A...

Still stuck: rerunning a query to change criteria
My form lets one set criteria for a query, from which some textboxes display several Dcounts of the selected clients (grouped and counted by age and gender). Now I need this query to be rerun once different criteria are set. But I believe that as long as the form is open, the query wil be run ones and then never again! I could of course have people close and open the form again and again, but that is cumbersome. Me next try would be to have the criteria be set i the form and then as next step run and open the query, including the data to be displayed in a subform. But is ...

Insert Database doesn't "see" half my queries?
I am trying to insert a database” from Access 03 into Word 03 using the database toolbar in word. When I click “Insert Database” in word, and then browse for my query, the wizard doesn’t “see” any of the queries in which I changed a field name (ex. Dept: Department). Does anyone know a workaround for this? Right now, it’s only showing about half my queries which is a little odd. Is there a different way to insert my database? Thank you! If you check Word Tools->Options->General->"Confirm conversion at open" and go through the whole insertion process aga...