Solved

Creating a spreadsheet-style subform from three tables

Posted on 2008-10-27
11
547 Views
Last Modified: 2013-11-29
I have a database for educational applications, and want to be able to see all scores for every student in a particular class section in a spreadsheet-like layout.

Among other tables, there is a ClassRoster table with the students in a particular section, and an Assignments table with the assignments in the particular class section.  Both of these are one-to-many to the Scores table.

ClassRoster 1->M Scores M<-1 Assignments

(The Assignments table is also many-to-one to the ClassSections table, and this chart would be a subform of a form based on ClassSections so the user would see only the assigments for a particular class section, but I don't think that matters.)

The chart I want to create would have assignments as columns, roster entries (that is to say, students) as rows, and items from the Scores table at the intersection of the two.

What would I use for this?  A crosstab query wouldn't work, I think -- there's no summary function for the intersections; the data comes from a different table.  Would I use a PivotTable?  And how, specifically, would I construct that as far as the query data source and such go?
0
Comment
Question by:slinkygn
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22824516
slinkygn,

Obviously, you will have to post a sample of your Database, and a sample of exactly what you want this output to look like.

JeffCoachman
0
 
LVL 6

Author Comment

by:slinkygn
ID: 22838635
OK, file included.  It's an .accdb file, but I had to rename it to .mdb so it would upload; you'll likely need to rename it back.

It's a pared-down version of what I have as far as the tables in question go.  There is also a "Score Chart" crosstab query that demonstrates how I'd want the scores showing in relation to the student IDs and assignments.  I'd want this sort of construction as a subform, so that I could see only the assignments for the current section record.

Hope that makes more sense.
demo.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22840641
The problem here is that there is no common field between the "Score Chart" crosstab query and the TBLSections table to synchronize on.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 6

Author Comment

by:slinkygn
ID: 22845527
It's trivial to add the fk_Section field to "Score Chart," which relates to the primary key in TBLSections.  That doesn't really change the primary issue of how to present the data.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22847528
OK, I tried it, and it works fine for me.

But if you are sure it won't work for you, then I guess we are at an impasse.

JeffCoachman
0
 
LVL 6

Author Comment

by:slinkygn
ID: 22847737
OK, I'll try this again.

Yes, it works as a solution to the relationship.  But that is not the problem I stated in the problem description.  I specifically said "I don't think that matters."

The question is, right now it's a crosstab query with a summary function for the values -- I don't want that.  I want the raw table values themselves as the cell values.  They are unmodifiable as a summary calculation.  I threw out the possibility of a PivotTable, or something else -- maybe a control that looks more spreadsheet-like?  I don't know.  That's the point of the question.

I thought I was clear enough about the parent/child relationship issue to Sections not mattering in the problem description when I said "I don't think it matters."  I just wanted to give as much information as I could in case there was some issue with a control or whatnot.  Perhaps I shouldn't have mentioned it at all.  My apologies.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22848089
OK, so let's try it this way:

Please post a sample with more data, it is hard to visualize:
  "all scores for every student in a particular class section"
...with only a few records in each table.
;-)

Then show me *exactly* what you want displayed for section: 1010.001

Thanks

JeffCoachman
0
 
LVL 6

Author Comment

by:slinkygn
ID: 22848141
How about:

I want it to display just like the crosstab query that's already there, but with the values editable.

That'd be a good start, I think.  We can go from there.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22855052
slinkygn,

By design, cross tab queries are non-updateable, so we are out of luck there.

JeffCoachman
0
 
LVL 6

Author Comment

by:slinkygn
ID: 22855087
Which is why I want to do it with a method *other* than a crosstab query.
0
 
LVL 6

Accepted Solution

by:
slinkygn earned 0 total points
ID: 22855106
I think I found a solution.

The following page:
http://msdn.microsoft.com/en-us/library/aa190078(office.10).aspx

details how to use the ActiveX Spreadsheet control in Access.  The calculate() method can be used to update objects within a field.  Obviously, the spreadsheet would have to first be populated onLoad(); that can be a nested iteration through the Scores table, I guess, pulling the assignment grades for each student.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

827 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