?
Solved

Editable CROSS JOIN query?

Posted on 2013-01-03
4
Medium Priority
?
479 Views
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.  

Thanks.
Jim
0
Comment
Question by:Jim Horn
  • 2
  • 2
4 Comments
 
LVL 120

Accepted Solution

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

When can I update data from a query?
http://msdn.microsoft.com/en-us/library/aa198446%28office.10%29.aspx
0
 
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.
0
 
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
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26487697.html#a33724604
0
 
LVL 66

Author Closing Comment

by:Jim Horn
ID: 38784240
Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 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