Novice needs help...

[danb]

Hi, just wondering if anyone could help with the following:

I have an access table with three columns: shops(1), and their easting(2) and northing(3) Ordnance Survey grid references.

I need to write a query which selects all shops within certain grid grid reference ranges (fine) but then performs a simple calculation on them. The calculation is simple trigonometry i.e. ((Easting^2)+(Northing^2)^0.5)=result.

I need to then send this result with the respective shop details to the browser, ordered by the result.

Can this be achieved? If so, could you explain to a newcomer how?

Thanks very much for your time chaps, much appreciated.

 

[danb]

I think I need something along the lines of:

SELECT * FROM Shops WHERE .....easting & northing within range,
((Easting^2)+(Northing^2)^0.5) AS CalculationResult,
ORDER BY CalculationResult;

The syntax here is crap (I'm not very familiar with SQL queries), and it obviously doesn't work. Still totally stuck, but maybe going in the right direction??

 

[dcx693]

You're heading in the right direction. What exactly is the criteria for "shops within certain grid grid reference ranges". The ((Easting^2)+(Northing^2)^0.5) AS CalculationResult part comes before the WHERE clause, but I wouldn't try to write the SQL from scratch. I'd do it in the query design view.

 

[danb]

For the Easting and Northing WHERE clause, I'll need to say something like the following (not sure how to write a variable which is being passed into the query, but it's something like @ValueBeingPassedIn, so I'll use the '@' sign to try and get this accross):


SELECT * FROM Shops WHERE Easting>=@EastingMin AND Easting<=@EastingMax AND Northing>=@NorthingMin AND Northing<=@NorthingMax,


How do I get this into the query whilst putting the calculation before the FROM clause?

 

[dcx693]

Well, a complete SQL statement for your situation might look something like this:
SELECT * ,((Easting^2)+(Northing^2)^0.5) AS CalculationResult FROM Shops WHERE (Easting>=@EastingMin AND Easting<=@EastingMax AND Northing>=@NorthingMin AND Northing<=@NorthingMax);

The only sticky point now is how to code for those variables being passed into the query. How are they being passed in? Through a parameter query prompt? A form? An Access table?

 

[danb]

Hmmm, I'm getting lost now...

I don't know if you can shed a bit of light on this, but I was hoping that I could pass the variable values into the query from an ASP web page. I did this a few years ago with a SQL stored procedure (not that I can even vaguely remember how!)

Is this possible and will it work in the example above? Also, I'm presuming that if the above works, then appending the ORDER BY clause to it will work as well...

Thanks for this, much appreciated.

 

[danb]

Okay, after a whole days Googling I'm nearly there.

I've got the values to be passed in with ADO, and my query looks like this:

SELECT (([Easting]^2)+([Northing]^2)^0.5) AS CalculationResult, *
FROM Shops
WHERE (Easting>=[EastingMin_IN] AND Easting<=[EastingMax_IN] AND Northing>=[NorthingMin_IN] AND Northing<=[NorthingMax_IN])
ORDER BY CalculationResult;


The ORDER BY doesn't work though. Fine when it's removed, but when it's there I get an error saying "Too few parameters. Expected 5." in the browser.

This is the crucial part of the whole thing, and I really do need to be able to order the results by the calculation result.

Does anyone know why this might be?

 

[Jon K]

SELECT (([Easting]^2)+([Northing]^2)^0.5) AS CalculationResult, *
FROM Shops
WHERE (Easting>=[EastingMin_IN] AND Easting<=[EastingMax_IN] AND Northing>=[NorthingMin_IN] AND Northing<=[NorthingMax_IN])
ORDER BY (([Easting]^2)+([Northing]^2)^0.5);

 

[danb]

Fantastic! Thanks.

One last thing... Can I get the CalculationResult back into the asp page? If so how?

I tried just pulling it in as part of the recordset along with all of the other data from the table:

rs("Easting") - fine
rs("Northing") - fine
rs("CalculationResult") - This just causes a 'page is experiencing problems / can't be found' error.

Any ideas??

 

[Jon K]

Maybe a temporary table would help. You can change the select query to a make-table query or an append query and populate the asp page from the temporary table.