DISTINCTROW query prevents editing of related fields

[thuja]

Here's a problem from an incipient revegetation planting calculator...

I want to query a list of only those records from [plant_list] that are found in [template_cover]. There may be many entries in [template_cover] for each in [plant_list]. BUT I want to be able to modify the values of [plant_list.spacing_id] in a subsequent form. When I use DISTINCTROW function to get the summary list it keeps me from modifying [plant_list.spacing_ID]!

heres the structure..

#1# [tbl_plants] a master plant list 400+ record

plant_id (key)
spacing_id (lookup to a spacing class table)


#2# [tbl_temp_plant_link]

template_link_ID (key)
plant_id
template_id
percent_cover

This SQL gets me the list but prevents me from editing tbl_plants.

SELECT DISTINCTROW tbl_plants.plant_ID
FROM tbl_plants INNER JOIN tbl_temp_plant_link ON tbl_plants.plant_ID=tbl_temp_plant_link.[plant_id]
GROUP BY tbl_plants.plant_ID;

Thanks for any assistance. let me know if this goes beyond the scope of the query forum

pc

 

[scriverb]

Because you are using DISTINCTROW designation you are basically grouping by the one field that you are selecting. Because of this the query cannot allow editing because it doesn't know which record you want edited. Since you have one field being listed just what do you want to edit. You certainly don't want to edit the ID field.

Please explain.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[thuja]

I am trying to edit tbl_plants.spacing_ID
It appears that whenever I use these query results I cannot edit any related fields in tbl_plants. Is there any way around this.

From the user end my goal is to have form with a list of 'used plants' and there parameters so that the values in tbl_plants can be edited without having to either look through the 400 record list, or have duplicates where plants are 'used' several times.

thanks for questions... I know far less then it may appear.

pc

 

[PHV]

And what about a query like this ?
SELECT * FROM tbl_plants
WHERE plant_ID IN (SELECT DISTINCT plant_ID FROM tbl_temp_plant_link);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244