Jim Horn
asked on
Editable CROSS JOIN query?
Hi All
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.
Table: comment
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.
Thanks.
Jim
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.
Table: comment
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.
Thanks.
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i think that would be the only way to do it.
see this similar process created by cyberkiwi for minesweeper
https://www.experts-exchange.com/questions/26487697/Minesweeper.html?anchorAnswerId=33724604#a33724604
see this similar process created by cyberkiwi for minesweeper
https://www.experts-exchange.com/questions/26487697/Minesweeper.html?anchorAnswerId=33724604#a33724604
ASKER
Thanks.
ASKER
I'm leaning towards the SP approach to edit cells anyways. The only disadvantage I can think of would be the time writing it + the time to refresh the form, but it saves me a lot of other headaches.