Solved

Editable CROSS JOIN query?

Posted on 2013-01-03
4
461 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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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 65

Author Comment

by:Jim Horn
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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 65

Author Closing Comment

by:Jim Horn
Comment Utility
Thanks.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 41
Complex SQL 10 32
Convert int to military time 8 20
Acess 2010 - Pause Until Part Of Event Completes 14 24
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now