Editable CROSS JOIN query?
Posted on 2013-01-03
Q: Is there such an animal as an editable CROSS JOIN / CROSSTAB query?
I have an Access 2010 FE - SQL 2010 BE project where I'm dealing with a 2D grid-like table, and each row-column combination (aka cell) can have multiple versions.
Columns: id (identity), row_id, column_id, value
The row_id and column_id isbasically a 2D grid, where I have to allow users to enter their own series of values (think the game Battleship, where user can create their own A2, A3, B2, B3, J2, J3, etc. out of a defined range of A-J and 1-10, where there will be either 0 or 1 rows for each A2, A3.. value)
I can display these series (aka A2, A3, B2, B3, J2, J3) using a CROSS JOIN or CROSSTAB query, but it will be read-only.
Without the ability to create an editable CROSS JOIN, I'm looking at creating a Stored Proc that accepts @row_id, @column_id, and @value, and inserting/deleting that way.