I can have many Questionares, and many Crops. A table named "Questionare-Crop" joins them.
The main form is for entering a Questionare. When I get to the Questionare-Crop form, I want to ORDER BY Crop names. So, I have the following SQL in the subform definition:
Code: Select all
SELECT q.Crop, Cultivated_area, Cultivated_area_unit, Metres2, Weight, Weight_unit, Kilograms
FROM Questionare_Crop q
LEFT JOIN Crops c on q.Crop = c.ID
WHERE q.Questionare = '#RECORD_ID#'
ORDER BY c.Crop
The first field is a connection to the Crop table. So I suspect that qualifying the Crop (with "q.Crop") is screwing things up, because the field name "Crop" refers to the crop name (VARCHAR) in the Crops table, and also the crop ID in the Questionare-Crop table. (Yea, I know. My bad.)
Should this sort of thing work? Do I need to "uniquify" the column names to make it work? (Don't wanna do that if I don't have to...)
Is there some other, more "approved" way of ordering subform grids?