-1

I'm getting a enter parameter value on cooltable.distance when Access executes ORDER BY, which is strange since distance is a column created in cooltable. What am I missing?

SELECT
target_postcodes.target_postcode,
population_postcodes.population_postcode,
cooltable.distance,
SQR( ( Population_postcodes.Longitude - target_postcodes.longitude)^2 + (Population_postcodes.Latitude - target_postcodes.latitude)^2 ) as distance

INTO
cooltable

FROM
Population_postcodes,
Target_postcodes


ORDER BY
cooltable.distance;
HansUp
  • 95,961
  • 11
  • 77
  • 135
Max F
  • 81
  • 2
  • 8
  • It looks like you're trying to retrieve data from the `cooltable` before it's been created with the `into` statement. If it already exists you probably want to use `insert` instead of `select ... into` – jpw Jan 08 '14 at 16:08
  • that's what I thought. Any suggestions on how to fix it? Maybe nesting it somehow? – Max F Jan 08 '14 at 16:09
  • 2
    aren't you missing a join on your 2 tables? – T McKeown Jan 08 '14 at 16:09
  • This is your third question on an identical issue : http://stackoverflow.com/questions/20996713/wrong-number-of-arguments-sql-msaccess, http://stackoverflow.com/questions/20994005/calculating-distance-pythagoras-and-running-count-in-sql-query#comment31549134_20994005 – Fionnuala Jan 08 '14 at 16:36

1 Answers1

0
INSERT INTO CoolTable( col1, col2, col3 )
SELECT .....
FROM ...
WHERE ...
T McKeown
  • 12,971
  • 1
  • 25
  • 32
  • number of query values and destination fields are not the same – Max F Jan 08 '14 at 16:16
  • no actually i had added a line in select that I deleted. It still asks me for the parameter value though – Max F Jan 08 '14 at 16:17
  • ok... then you have a mismatch of columns specified in the INSERT INTO CoolTable( Col1, col2, col3) and the SELECT. In my example here we have 3. So 3 columns on the INSERT and 3 columns on the SELECT – T McKeown Jan 08 '14 at 16:18
  • Yep i've solved that but still getting the parameter value prompt – Max F Jan 08 '14 at 16:19
  • is there a menu option for Parameters? Did you manually add a parameter? Maybe you should start over – T McKeown Jan 08 '14 at 16:19