Can't update data returned by SELECT query

[ScottT]

I have the following query:

SELECT fl.File_Name, sm.Who, v.Long_Vin, v.Short_Vin, v.Reg_Date, sm.Finance_Comment, sm.Fleet_Admin_Error_ID, sm.Allocated_to_ID, sm.Resolution_Status_ID, sm.Fleet_Admin_Note, sm.Resolution_Status_Note, prk.current_assigned_to AS parked_note, susp.current_assigned_to AS suspense_note, prk.Current_Status AS Parked_Status, sm.Last_Updated
FROM 
((((SIMPLE_MASTER AS sm INNER JOIN FILES AS fl ON sm.file_ID = fl.file_ID) 
INNER JOIN Vehicles AS v ON sm.vehicle_id = v.vehicle_id) 
LEFT JOIN PARKED AS prk ON Ucase(v.Long_Vin) = Ucase(prk.Long_Vin)) 
LEFT JOIN SUSPENSE AS susp ON Ucase(v.Long_Vin) = Ucase(susp.Long_Vin))
ORDER BY sm.Import_Date;

I cannot edit the data returned by this query, and I am logged in as admin. I have checked the privileges on the tables and query and have full rights.

The joins between tables are pretty straightforward.

Vehicle_id is a number field (int)
Long_Vin is text (255)


Other SELECT queries let me edit the data in-place, why can't I edit the data returned from this one?

 

[ScottT]

Hmm it's put this post in the Tables section, not queries. Could a kind mod please move this? Thanks <embarrassed look>

 

[JANR]

You query isen't updateble because your joins goes in all directions, see this link : http://allenbrowne.com/ser-61.html

JR

 

[ScottT]

You query isen't updateble because your joins goes in all directions, see this link : http://allenbrowne.com/ser-61.html

JR

Hi JR, thanks for your reply.

So does that mean to make the data editable I'd have to denormalise the data?

Or is there another way, without changing the table schema?

It's a bit late in the dev lifecycle for me to go moving everything into "flat" structures again, so any advice would be appreciated.

 

[ScottT]

OK, I've removed the LEFT JOINs and thats allowed the data to be editable; luckily the LEFT JOIN tables can be considered superfluous to the result set, but ideally I'd like to have an editable result set with that data in it.

I wonder if this is possible? Hmm

 

[Galaxiom]

Use a subform to display the data from the related records.