Solved

Creating a spreadsheet-style subform from three tables

Posted on 2008-10-27
11
555 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

739 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