Generating a new table via crosstab query (1 Viewer)

Terry Lawson · Nov 22, 2017

I'm trying to generate a crosstab (pivot?) table (WorktableB) from an existing table (WorktableA) in vba.

First time I've tried this sort of thing!

My code results in run time error 3126.

Suggestions pls.

Code:

Dim SQLText As String
Dim WorktableACrosstab As String

'generate a crosstab query for worktableA
WorktableACrosstab = "TRANSFORM First(WorktableA.Qty) AS FirstOfQty " & _
"SELECT WorktableA.[Line Numb], WorktableA.[Part Number], WorktableA.Supplier " & _
"FROM WorktableA " & _
"GROUP BY WorktableA.[Line Numb], WorktableA.[Part Number], WorktableA.Supplier " & _
"ORDER BY WorktableA.[Line Numb] " & _
"PIVOT WorktableA.[Kit no];"

'make a new table from the crosstab query
SQLText = "SELECT WorktableA_Crosstab.* INTO WorktableB " & _
"FROM '" & WorktableACrosstab & "';"
DoCmd.RunSQL (SQLText)

pbaldy · Nov 22, 2017

Never tried this, but you wouldn't want the single quotes around the SQL variable. Try:

"FROM " & WorktableACrosstab & ";"

or maybe

"FROM (" & WorktableACrosstab & ")"

This may help:

http://www.baldyweb.com/ImmediateWindow.htm

isladogs · Nov 22, 2017

Yes its possible to create tables based on crosstab queries:
The student assessment form shown in this screenshot is actually based on that idea plus some additional functionality

The relevant parts to your question are
a) crosstab query

Code:

TRANSFORM Max(qryAssessmentMarks.Mark) AS MaxOfMark
SELECT qryAssessmentMarks.PupilID, qryAssessmentMarks.Surname, qryAssessmentMarks.Forename, qryAssessmentMarks.Gender, qryAssessmentMarks.YearGroup, qryAssessmentMarks.TutorGroup, qryAssessmentMarks.CourseID, qryAssessmentMarks.ClassID, qryAssessmentMarks.SubjectID, 0 AS Completed, Sum(qryAssessmentMarks.Mark) AS TotalMarks, Avg(qryAssessmentMarks.Mark) AS AverageMark, '' AS ClassRank, '' AS CourseRank
FROM qryAssessmentMarks
WHERE (((qryAssessmentMarks.AssName)<>'') AND ((qryAssessmentMarks.ClassID)=GetClass()))
GROUP BY qryAssessmentMarks.PupilID, qryAssessmentMarks.Surname, qryAssessmentMarks.Forename, qryAssessmentMarks.Gender, qryAssessmentMarks.YearGroup, qryAssessmentMarks.TutorGroup, qryAssessmentMarks.CourseID, qryAssessmentMarks.ClassID, qryAssessmentMarks.SubjectID
ORDER BY qryAssessmentMarks.Surname, qryAssessmentMarks.Forename
PIVOT qryAssessmentMarks.AssName;

b) make table query for the form

Code:

CurrentDb.Execute "SELECT qryClassAssessmentMarks_Crosstab.* INTO tblClassAssessmentMarksTEMP" & _
        " FROM qryClassAssessmentMarks_Crosstab;"

So as you can see it's the same idea as yours.
I just saved the query to make my life easier

I think your issue is very simple - inconsistent naming
WorktableACrosstab & WorktableA_Crosstab
Also the FROM line is completely wrong

The make table part should just be:

Code:

[B]SQLText = "SELECT WorktableACrosstab.* INTO WorktableB" & _
" FROM WorktableACrosstab;[/B]"

Now, I thought that doing .* on a string wouldn't work.
So I tested it by running the same idea on my code as follows

Code:

Sub TESTCrosstabMakeTable()

Dim qryClassAssessmentMarks_Crosstab As String

strClass = "07BN/Sc"

qryClassAssessmentMarks_Crosstab = "TRANSFORM Max(qryAssessmentMarks.Mark) AS MaxOfMark" & _
    " SELECT qryAssessmentMarks.PupilID, qryAssessmentMarks.Surname, qryAssessmentMarks.Forename, qryAssessmentMarks.Gender," & _
    " qryAssessmentMarks.YearGroup, qryAssessmentMarks.TutorGroup, qryAssessmentMarks.CourseID, qryAssessmentMarks.ClassID," & _
    " qryAssessmentMarks.SubjectID, 0 AS Completed, Sum(qryAssessmentMarks.Mark) AS TotalMarks," & _
    " Avg(qryAssessmentMarks.Mark) AS AverageMark, '' AS ClassRank, '' AS CourseRank" & _
    " FROM qryAssessmentMarks" & _
    " WHERE (((qryAssessmentMarks.AssName)<>'') AND ((qryAssessmentMarks.ClassID)=GetClass()))" & _
    " GROUP BY qryAssessmentMarks.PupilID, qryAssessmentMarks.Surname, qryAssessmentMarks.Forename," & _
    " qryAssessmentMarks.Gender, qryAssessmentMarks.YearGroup, qryAssessmentMarks.TutorGroup," & _
    " qryAssessmentMarks.CourseID, qryAssessmentMarks.ClassID, qryAssessmentMarks.SubjectID" & _
    " ORDER BY qryAssessmentMarks.Surname, qryAssessmentMarks.Forename" & _
    " PIVOT qryAssessmentMarks.AssName;"
    
CurrentDb.Execute "SELECT qryClassAssessmentMarks_Crosstab.* INTO tblClassAssessmentMarksTEMP" & _
        " FROM qryClassAssessmentMarks_Crosstab;", dbFailOnError

End Sub

It worked!

Even so, I recommend you SAVE the crosstab query as I did as e.g. qryWorktableACrosstab then use that in the make table query

HTH

Terry Lawson · Nov 23, 2017

Forum as brilliant as ever, many thanks to you both.

Problem solved by storing the crosstab and then calling that in the make table, having first corrected the stupid inconsistency.

isladogs · Nov 23, 2017

Glad it worked & that you went with saving the crosstab query.

I was frankly amazed that doing .* on a string worked at all so I also learned something!

Generating a new table via crosstab query (1 Viewer)

Terry Lawson

Registered User.

pbaldy

Wino Moderator

isladogs

MVP / VIP

Attachments

Terry Lawson

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread