Editable cross-tab?

I have two tables that are one-to-many with a third join table.  I can put these in a cross-tab, with the two one-side tables as the row and column labels and the join table as the values.  (Please see demo attached in related question for an example.)  But since the values of the cross-tab have to be a summary function on the join table values (I used either "First" or "Max", I think), they are not editable.

If I wanted to present the data in a similar fashion, but have the values be editable, how would I do that?

In the related question, I took a look at the Spreadsheet control.  If there are other (simpler) ways to do this, how to do so (detail preferred) would answer the question.  Otherwise, details on the Spreadsheet control's usage for an application like this would suffice.
Who is Participating?
nmcdermaidConnect With a Mentor Commented:
OK I looked at your other post, I understand a little better.
There is no issue that a given cell is indeed an actual single value in the underlying table, so you can identify the record to be changed in the underlying table from the crosstab cell.... I was worried that you were trying to change a summary value and apply it to detail values.
I think the only solution for you is to try both the crosstab query and the spreadsheet control, and see if you can capture a click event on a given cell. Then you can identify the cell column and row, then you can update the database totally external to the control.
I don't think you are going to find a control that will natively do what you do because as you say they are all about showing crosstabs and summarising detail data.
Well one way is to load the data into SQL Server Analysis services. Then you can report on the cube on Excel (as a crosstab) and I believe you can write back to the cube as well.
slinkygnPresidentAuthor Commented:
It seems that any crosstabs I make require a summary function for the intersection values. How would I get around that?  Would a crosstab from a SQL Server source be different than one from an Access source?

And I guess I'd need an instance of SQL Server running for that, right?
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Regarding the summary function: if you do edit some data in a crosstab, how does it know where to write the data back to?
Regarding Analysis services: Its a particular service that gets (optionally) gets installed by SQL Server.
The reason I mention Analysis Services is that it has a 'splashback' function - when you edit a value at a summary level, it 'splashes the data down to lower levels according to whatever rule you want to use, i.e, the total figure can be evenly distributed among lower members, it can be weighted by existing values etc.
But forget about anlysis services for now.
Are you actually showing numbers or text in your crosstab? You'll need to work out what summary function you will be using before you can work out how you want values to be written back.
slinkygnPresidentAuthor Commented:
That's precisely the problem.  I *can't* edit data in a crosstab.  Since the value data in the cells is a calculation ( Summary([field]) ), the field is uneditable.  And at least as far as I could surmise, there's no way to just use table data straight.  Is there some way around this I don't know about?

The "splashback" bit sounds interesting, but I'm 99% sure I can't convince anyone to install SQL Server on a machine -- they're not going to want to maintain it.
slinkygnPresidentAuthor Commented:
Heeeey.  Straightforward, sounds like it would work, and it has that "man, I should've thought of that" simplicity.  Capture the click event.  That's the best idea I've heard yet.  I'm going to give that a shot; will report back.
Now you're just fighting against the 'custom control dumbo factor' where the control developer has disabled some important element (like being able to capture a click event) cause they thought no one would use it!
Good Luck
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.

All Courses

From novice to tech pro — start learning today.