Solved

Creating a spreadsheet-style subform from three tables

Posted on 2008-10-27
11
551 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 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