Solved

Creating a spreadsheet-style subform from three tables

Posted on 2008-10-27
11
537 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now