Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Editable CROSS JOIN query?

Posted on 2013-01-03
4
Medium Priority
?
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…
Suggested Courses

636 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