Query With Parameters In A Form
- Thread starter Martyh
- Start date
Martyh
Registered User.
- Local time
- Today, 02:14
- Joined
- May 2, 2000
- Messages
- 196
Query with parameters - how do you ...
MILE .... I've decided to post the written part. Thanks for the suggestion....
First some background:
I got an X and Y cordinate... and I've got a radius (of uncertainty). What I need to know is how many of these points, about 60,000 points in the database and also with X and Y coordinates fall within the "radius of uncertainty.
r = SQRT( (x-a)**2 + (y-b)**2)
where (x,y) equals the coordinates of the centre of the circle
and (a,b) equals the coordinates of one of the data points
and r = the straight line distance between the two;
The calculation of r is made and then tested against the radius of uncertainty.
************************************************
I'm not sure how this code will be written... but
************************************************
I will follow my thinking in making the calculations - there are 3 queries involved qryFindInRad, qryFindInRad2 and lastly qryFindInRad3. Please see the mdb file for this.
Example Input for qryFindInRad
These are the three parmeters:
DblY : 5009000 (Easting)
DblX : 445000 (Northing)
DblRadius: 1000
This will result in query which will go thru the entire db table (wwr) and calculate the actual distance to a "proposed" well. There are currently 60,000 wells in the wwr records, and takes a long time which is unacceptable... The list is sorted by Radius.
Figure 1
ID OWNER EASTING NORTHING Radius
1514483 HERBERT J G 454680 5008855 351
1518185 ST AMOUR R 454499 5008899 511
...
...
1516878 POWERS M 454420 5007960 1191
1514884 FRALAN STE 454056 5009991 1369
... And so on
(NOTE: In this version, I was forced to cut down the number of wells.)
But I am particularly interested in the ones that are close to the proposed well, say within 1000 m. So what I am doing is "chopping off" those which are outside of the 1000 m area and therefore are not included in calculation which takes a lot of time.
The second query (qryFindInRad2) provides the union between the X and Y .
Figure 2
ID OWNER EASTING NORTHING Radius
1514483 HERBERT J G 454680 5008855 351
1518185 ST AMOUR R 454499 5008899 511
...
1516813 POWERS M 454440 5008000 1146
This leaves the 19 wells, for which the calculation needs to be made. The last three wells on the list will be taken off because the calculation tells us that they are outside of 1000 m. Thus the third query, qryFindInRad3 and the results are as follows:
Figure 3
ID OWNER EASTING NORTHING Radius
1514483 HERBERT J G 454680 5008855 351
1518185 ST AMOUR R 454499 5008899 511
...
1510689 ARLAND C 455000 5008020 980
1510478 NEULIST FR 454540 5008120 993
Or 16 wells that are within 1000 m of the "proposed" well.
All is good ... until I try to place this query into a form.
The situation is that when I try to get from the primary screen (fprifrmProject) to the screen which should allow me to access these 16 wells (Find Wells (N&&E): frmFindWell).
I can't figure out how to do this properly!! I think I require some heavy duty programming HELP ...
TIA
Marty H.
MILE .... I've decided to post the written part. Thanks for the suggestion....
First some background:
I got an X and Y cordinate... and I've got a radius (of uncertainty). What I need to know is how many of these points, about 60,000 points in the database and also with X and Y coordinates fall within the "radius of uncertainty.
r = SQRT( (x-a)**2 + (y-b)**2)
where (x,y) equals the coordinates of the centre of the circle
and (a,b) equals the coordinates of one of the data points
and r = the straight line distance between the two;
The calculation of r is made and then tested against the radius of uncertainty.
************************************************
I'm not sure how this code will be written... but
************************************************
I will follow my thinking in making the calculations - there are 3 queries involved qryFindInRad, qryFindInRad2 and lastly qryFindInRad3. Please see the mdb file for this.
Example Input for qryFindInRad
These are the three parmeters:
DblY : 5009000 (Easting)
DblX : 445000 (Northing)
DblRadius: 1000
This will result in query which will go thru the entire db table (wwr) and calculate the actual distance to a "proposed" well. There are currently 60,000 wells in the wwr records, and takes a long time which is unacceptable... The list is sorted by Radius.
Figure 1
ID OWNER EASTING NORTHING Radius
1514483 HERBERT J G 454680 5008855 351
1518185 ST AMOUR R 454499 5008899 511
...
...
1516878 POWERS M 454420 5007960 1191
1514884 FRALAN STE 454056 5009991 1369
... And so on
(NOTE: In this version, I was forced to cut down the number of wells.)
But I am particularly interested in the ones that are close to the proposed well, say within 1000 m. So what I am doing is "chopping off" those which are outside of the 1000 m area and therefore are not included in calculation which takes a lot of time.
The second query (qryFindInRad2) provides the union between the X and Y .
Figure 2
ID OWNER EASTING NORTHING Radius
1514483 HERBERT J G 454680 5008855 351
1518185 ST AMOUR R 454499 5008899 511
...
1516813 POWERS M 454440 5008000 1146
This leaves the 19 wells, for which the calculation needs to be made. The last three wells on the list will be taken off because the calculation tells us that they are outside of 1000 m. Thus the third query, qryFindInRad3 and the results are as follows:
Figure 3
ID OWNER EASTING NORTHING Radius
1514483 HERBERT J G 454680 5008855 351
1518185 ST AMOUR R 454499 5008899 511
...
1510689 ARLAND C 455000 5008020 980
1510478 NEULIST FR 454540 5008120 993
Or 16 wells that are within 1000 m of the "proposed" well.
All is good ... until I try to place this query into a form.
The situation is that when I try to get from the primary screen (fprifrmProject) to the screen which should allow me to access these 16 wells (Find Wells (N&&E): frmFindWell).
I can't figure out how to do this properly!! I think I require some heavy duty programming HELP ...
TIA
Marty H.
