Update Crosstab Queries!

[ShaneBrennan]

I have a little problem - Don't you hate problems that start like this?

I want to create something similarto a cross tab query where I have a grid, showing students names down the left and the names of assignments at the top and the grades the students have received for these assignments.

I can create a cross tab to show this information but I can not enter the data into the results of the query to update the tables.

Does anyone have any idea how I can create something to solve this little problem.

Thank you for any help in advance

Shane.


Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[MichaelRed]

Aggregate queries are NEVER Updateable. You really need to redesign the database (e.g. "NORMALIZE" it). You can not generate a single table of students and assignments and get there from here (or anywhere else). The students and assignments need to be seperate tables. You will also need a bridge/link table for StudentId to AssignmentID. When properly linked, this will "look like". the crosstab query you are thinking about.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ShaneBrennan]

I have normalised the table as follows:

tblAssignments has a list of assignment codes and descriptions
tblStudents has all the necessary student details and
tblresults has the link between tblAssignments & tblStudents with their awarded grade.

Example contents of tblResults:

AssignmentID StudentID Grade
AVCE1-1-1 1 P
AVCE1-1-1 2 P
AVCE1-1-1 3 P
AVCE1-1-1 4 M
AVCE1-1-1 5 P
AVCE1-1-2 4 P
AVCE1-1-2 5 P

I wish to create a table/query or form that display and allow the UPDATE of the tblResults, in a format similar to the following:

StudentID AVCE1-1-1 AVCE1-1-2
1 P
2 P
3 P
4 M P
5 P P

Shane

Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[ShaneBrennan]

I have had a little bit of a break through with this one. I have explained it in the forum Microsoft Access Tables and relationships.

I have managed to create a table which I can now update in the desired format. I don't know how to update the original table with the new results.

Any suggestions whould be greatfully appreciated.

Shane

Shane Brennan
Shane.Brennan@tcat.ac.uk