?
Solved

Editable cross-tab?

Posted on 2008-11-10
8
Medium Priority
?
571 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:slinkygn
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22928064
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
 
LVL 6

Author Comment

by:slinkygn
ID: 22928618
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22935537
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Author Comment

by:slinkygn
ID: 22936627
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 22936861
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
 
LVL 6

Author Comment

by:slinkygn
ID: 22937020
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22937052
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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