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.
LVL 6
slinkygnPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
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.
0
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?
0
nmcdermaidCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
0
nmcdermaidCommented:
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.
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
nmcdermaidCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.