Creating a table from a table! 1

[ShaneBrennan]

I have a ickle bickle table with the following fields:

CourseID
UnitID
StudentID
EVCode
Mark

I want to create a new table from this information with:

CourseID
UnitID
StudentID
**Then a new field for each EVcode

Example

CourseID UnitID StudentID EVCode Mark
xxxxxxxxx xxxxxx xxxxxxx e01 999
xxxxxxxxx xxxxxx xxxxxxx e02 999
xxxxxxxxx xxxxxx xxxxxxx e03 999
xxxxxxxxx xxxxxx xxxxxxx e04 999
xxxxxxxxx xxxxxx xxxxxxx a01 999
xxxxxxxxx xxxxxx xxxxxxx a02 999


to produce a table with the following fields:

CourseID
UnitID
StudentID
E01
E02
E03
E04
A01
A02


I need this table, so I can put the results of a cross tab into the correct sequence.

Another possibility is to find a way of sequencing the EVCode generated fields into the correct sequence. As If I create a CROSS TAB query from the original table I get:

CourseID
UnitID
StudentID
A01
A02
E01
E02
E03
E04

Where the fields are generated in ascending order. It's important, from the users prospecive that the E01, E02, A01, A02 are in the correct sequence.

I would appreciate any help with this

Many thanks

Shane


Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[ShaneBrennan]

When reading another CrossTab query question I read that you can specify the field headings - which I've done, by entering e01, e02, e03....a01, a02 in the 2nd row "Column Headings" in the "Query Properites" dialog.

However this is okay, but the field headings will change for each implementation of the program.

I've created another query called "qryAllUniqueEvidenceCodes", is there someway to read the records in this table and place these headings automatically in the Column Headings?

At a guess I'll have to write some VBA, again any help would be appreciated

Shane Brennan
Working hard and getting a headache!


Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[ShaneBrennan]

WEEEEEEEEEEEEEEEEEEEEEEEEEE DID IT!

I had to create VBA to solve it, but I did the blinking thing!

Here's the code I used, in case anyone needs something similar in the near future

Private Sub Command0_Click()

' Create Counters
Dim Counter As Integer

' Source Table
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryAllUniqueEvidenceCodes&quot

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "TRANSFORM First([tmp-AssessedWork].Mark) AS FirstOfMark SELECT [tmp-AssessedWork].CourseID, [tmp-AssessedWork].StudentId, [tmp-AssessedWork].UnitID FROM [tmp-AssessedWork], qryAllUniqueEvidenceCodes GROUP BY [tmp-AssessedWork].CourseID, [tmp-AssessedWork].StudentId, [tmp-AssessedWork].UnitID ORDER BY [tmp-AssessedWork].CourseID, [tmp-AssessedWork].StudentId, [tmp-AssessedWork].UnitID, [tmp-AssessedWork].Code PIVOT [tmp-AssessedWork].Code In ("

' BuildUp the Names of the Headings - in correct order
rst.MoveFirst
strSQL = strSQL & rst![TheCode]
rst.MoveNext

' loop the remaining records & add data to list
Do Until rst.EOF
strSQL = strSQL & "," & rst![TheCode]
rst.MoveNext
Loop

' Finish the strSQL off properly
strSQL = strSQL + ""

' Use the CreateQueryDef command to create a query called qryAllAssessedGrades
Set qdf = dbs.CreateQueryDef("qryAllAssessedGrades", strSQL)


End Sub


The initial strSQL was generated by the QBE - So I'm not responsible for that one.

Shane
Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[dawnd3]

Thanks for posting your process with this once you figured it out! Dawn