Interpolation Query...

  • Thread starter Thread starter DIOS
  • Start date Start date
OK, I follow the query now and it works well. I just needed to
restrict the site and i get the correct calcualtion. The only thing I
see that does not ork is the boundary conditions because there is
nothing above or below it. For example if my boundary was 55 and 410
at a value of 0.80 then the query returns no records basically because
it cannot find a record "above" this one. Not sure how I can account
for the boundary conditions. this is more difficult than I thought.

Abel




Michel Walsh said:
Hi,


You are absolutely right, the four auxiliaries provide the max and the
min. You should add the site condition in those queries AND in the top most
query, just like:

...
FROM ( SELECT x, y, u
FROM xyData
WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin)
AND Site=TheSite ) As a,



Each of these virtual tables,

SELECT x, y, u
FROM xyData
WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin)
AND Site=TheSite

should return just one record. If not, there is probably a duplicated
(Site, x, y) value, but you can still add a DISTINCT:

SELECT DISTINCT x, y, u
FROM xyData
WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin)
AND Site=TheSite




Hoping it may help,
Vanderghast, Access MVP

DIOS said:
Well Im running some tests that are limited to certain sites and I am
getting multiple records back. If I do not specicfy the site i get
even more records back. I follow your interpolation math but the SQL
query is a little bit over my head and im trying to grasp what its
doing. The 4 auxiliaries are providing the max and mins for my two
variables and then my main query will do the interpolation correct?
Why then do i get multiple records back. They look to be repeated as
well.

AGP

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
Hi,

I am glad it worked.

Indeed, with ... FROM ( SELECT ... ), this is a known bug... you have to
edit the SQL statement in the SQL view only. If you edit it from the design
view, you are ... done. And that applies only for Jet (the design interface
with MS SQL Server does not have that bug).


Vanderghast, Access MVP

The message is "The Microsoft Jet database engine cannot find the
input table or query 'SELECT x, y, u FROM xyData WHERE x IN( SELECT *
FROM Qxmin ) AND y IN ( SELECT * FROM Qymin)'".

The SQl take out of the designer is as fllows:
SELECT
0.25*((1-[xi])*(1-[eta])*a.u+(1+[xi])*(1-[eta])*b.u+(1+[xi])*(1+[eta])*c.u+(
1-[xi])*(1+[eta])*d.u)
AS uw, 2*([xw]-a.x)/(b.x-a.x)-1 AS xi, 2*([yw]-a.y)/(d.y-a.y)-1 AS eta
FROM (SELECT x, y, u FROM xyData WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin) ) AS a, (SELECT x, y, u FROM xyData
WHERE x IN( SELECT * FROM Qxmax ) AND y IN ( SELECT * FROM Qymin) )
AS b, (SELECT x, y, u FROM xyData WHERE x IN( SELECT * FROM Qxmax )
AND y IN ( SELECT * FROM Qymax) ) AS c, (SELECT x, y, u FROM xyData
WHERE x IN( SELECT * FROM Qxmin ) AND y IN ( SELECT * FROM Qymax) )
AS d;

I changed all the square brackets to parenthesis and it worked!!! yet
when I reopen the qury in the designer the brackets are back. So
everytime i edit the query i have to make sure the brackets are
replaced with parenthesis. Weird. let me test some more since im going
to be actually calling the queries through code rather than through
the query designer.

AGP
Click to expand...
Click to expand...
Click to expand...
 
Hi,


A possible modification is to change the > to >= in the Q?max WHERE
clause. Doing so, you must also avoid possible division by zero in the
xi-eta computation:


SELECT ...

iif(b.x=a.x, 1, 2*([xw]-a.x)/(b.x-a.x)-1 ) AS xi,
iif(d.y=a.y, 1, 2*([yw]-a.y)/(d.y-a.y)-1 ) AS eta


FROM ...




Hoping it may help,
Vanderghast, Access MVP



DIOS said:
OK, I follow the query now and it works well. I just needed to
restrict the site and i get the correct calcualtion. The only thing I
see that does not ork is the boundary conditions because there is
nothing above or below it. For example if my boundary was 55 and 410
at a value of 0.80 then the query returns no records basically because
it cannot find a record "above" this one. Not sure how I can account
for the boundary conditions. this is more difficult than I thought.

Abel




"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
Hi,


You are absolutely right, the four auxiliaries provide the max and the
min. You should add the site condition in those queries AND in the top most
query, just like:

...
FROM ( SELECT x, y, u
FROM xyData
WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin)
AND Site=TheSite ) As a,



Each of these virtual tables,

SELECT x, y, u
FROM xyData
WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin)
AND Site=TheSite

should return just one record. If not, there is probably a duplicated
(Site, x, y) value, but you can still add a DISTINCT:

SELECT DISTINCT x, y, u
FROM xyData
WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin)
AND Site=TheSite




Hoping it may help,
Vanderghast, Access MVP

DIOS said:
Well Im running some tests that are limited to certain sites and I am
getting multiple records back. If I do not specicfy the site i get
even more records back. I follow your interpolation math but the SQL
query is a little bit over my head and im trying to grasp what its
doing. The 4 auxiliaries are providing the max and mins for my two
variables and then my main query will do the interpolation correct?
Why then do i get multiple records back. They look to be repeated as
well.

AGP

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
Click to expand...
Hi,

I am glad it worked.

Indeed, with ... FROM ( SELECT ... ), this is a known bug... you have to
edit the SQL statement in the SQL view only. If you edit it from the design
view, you are ... done. And that applies only for Jet (the design interface
with MS SQL Server does not have that bug).


Vanderghast, Access MVP

The message is "The Microsoft Jet database engine cannot find the
input table or query 'SELECT x, y, u FROM xyData WHERE x IN( SELECT *
FROM Qxmin ) AND y IN ( SELECT * FROM Qymin)'".

The SQl take out of the designer is as fllows:
SELECT
Click to expand...
0.25*((1-[xi])*(1-[eta])*a.u+(1+[xi])*(1-[eta])*b.u+(1+[xi])*(1+[eta])*c.u+(
1-[xi])*(1+[eta])*d.u)
AS uw, 2*([xw]-a.x)/(b.x-a.x)-1 AS xi, 2*([yw]-a.y)/(d.y-a.y)-1 AS eta
FROM (SELECT x, y, u FROM xyData WHERE x IN( SELECT * FROM Qxmin )
AND y IN ( SELECT * FROM Qymin) ) AS a, (SELECT x, y, u FROM xyData
WHERE x IN( SELECT * FROM Qxmax ) AND y IN ( SELECT * FROM Qymin) )
AS b, (SELECT x, y, u FROM xyData WHERE x IN( SELECT * FROM Qxmax )
AND y IN ( SELECT * FROM Qymax) ) AS c, (SELECT x, y, u FROM xyData
WHERE x IN( SELECT * FROM Qxmin ) AND y IN ( SELECT * FROM Qymax) )
AS d;

I changed all the square brackets to parenthesis and it worked!!! yet
when I reopen the qury in the designer the brackets are back. So
everytime i edit the query i have to make sure the brackets are
replaced with parenthesis. Weird. let me test some more since im going
to be actually calling the queries through code rather than through
the query designer.

AGP
Click to expand...
Click to expand...
Click to expand...
 
Back
Top