Editable CROSS JOIN query?

Posted on 2013-01-03
Medium Priority
Last Modified: 2013-01-16
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.  

Question by:Jim Horn
  • 2
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 38741389
don't know if you have seen this yet, just for reference and possible solution

When can I update data from a query?
LVL 66

Author Comment

by:Jim Horn
ID: 38741474
Helpful for other purposes, thanks, but for this one it confirmes that crosstab queries are read only.

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.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38741596
i think that would be the only way to do it.

see this similar process created by cyberkiwi for minesweeper
LVL 66

Author Closing Comment

by:Jim Horn
ID: 38784240

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question