Solved

Editable CROSS JOIN query?

Posted on 2013-01-03
4
464 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 500 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 65

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 65

Author Closing Comment

by:Jim Horn
ID: 38784240
Thanks.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 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