Editable Crosstab Control (Bound to underlying tables)
My idea is similar to the one called "Provide a native grid control similar to the visual studio gridview or even the old flexgrid" EXCEPT I want it to be a BOUND crosstab that is editable. This blog describes how to fake an editable crosstab, but it is a lot of work http://alisadev.blogspot.com/2009/06/how-to-fake-editable-crosstab-in.html. My best example is that for a school, they want to view the grades in a grid, but be able to edit them. Classes go down the side, Quarters go across the top, and Grades will be in the middle. When the user is hand-entering the Grades for Quarter 2, the user wants to be able to see the existing grade for Q1 next to it. I could create this by dumping the data into temp tables and creating a crosstab view, but then it is not editable because the crosstab is not bound back to the underlying normalized tables. This is not summary data (like # sales per month per employee), but just data from normalized tables being presented in an editable grid. The solution / process would be to take the data from normalized tables, de-normalize it and present it in an editable crosstab (with maybe a temp table behind it), and then normalize the data again and save it back to the source tables.
Traci Dysart
shared this idea
5 comments
-
Colin Riddington
commented
I have done exactly what you're asking for a school assessment mark database.
Starting with normalised tables, I create a crosstab query then create a temp table based on that which is then used as the record source for an editable form. As changes are made the original data is automatically updated. If interested, you can find a demo of my School Data Analyser database which includes this feature at
http://www.mendipdatasystems.co.uk/demo-versions/4585524220Colin
-
Kenneth Barber commented
I gave this +3 votes. It's something that I am quite passionate about, more so on the Excel side of things.
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10719492-pivottable-able-crosstab-data-entryBasically, to make such an idea work, you need to specify multiple columns to act as a primary key. Since this is Access, these would probably all be foreign keys to the tables containing the text values that you would want to appear as the crosstab headings.
-
Ben Sacheri
commented
Traci,
I have created an editable crosstab based on the blog posting from Alisa. I created two YouTube videos so others could see how it works.
5 minute quick demo
https://www.youtube.com/watch?v=6kOVw8tLSJ81 hour demonstration and troubleshooting session
https://www.youtube.com/watch?v=paF2NVaI01s -
Traci Dysart
commented
I know it's difficult....For summary data, it is impossible, but for non-summarized data, it would be possible, but a lot of work. And I don't know how often users would need this kind of interface. For the school scenario I mentioned above, I originally used normalized tables, but then I had to de-normalize the tables in order to create an editable spreadsheet-type interface. It just bothered me to de-normalize the tables.
-
Mark Burns
commented
Traci,
The edit-ability of a set of crosstab results would be highly problematic in an of itself.
The UI is one issue, but the edit-ability of the underlying query results is actually a far more difficult challenge to achieve. This is particularly noticeable in that the key ID fields and similar associations are often lost/tossed aside in the shuffle of compiling the crosstab results, particularly when summaries or other domain functions are employed in generating the crosstab results.