Solved

Creating a spreadsheet-style subform from three tables

Posted on 2008-10-27
11
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
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…

624 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