Crosstab Query groups and totals

I have been trying to create a crosstab query for a competition point results with rows containing members and colums containing entry titles. In each of the competitions only 3 entrys are made by each member (EntryTitle) so I would like to group entry titles column headings to "Image1","Image2","Image3","Points Total"
The problem I am having is grouping the entrytitles into the three columns. Any ideas how this can be done or solution?
LVL 2
MGardnerAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Try this query:
TRANSFORM Sum(tblEntrants.Points) AS SumOfPoints
SELECT tblEntrants.MemberID, tblEntrants.CID, Sum(tblEntrants.Points) AS TotalPints
FROM tblEntrants
WHERE (((tblEntrants.CID)=13))
GROUP BY tblEntrants.MemberID, tblEntrants.CID
ORDER BY tblEntrants.MemberID
PIVOT "Image" & DCount("*","tblEntrants","[CEID]<=" & [CEID] & " AND [CID] = " & [CID] & " AND [MemberID] = " & [MemberID]) In ("Image1","Image2","Image3");

Open in new window

0
 
MGardnerAuthor Commented:
Thought I attached a jpeg of the query I have so far
A-B004.bmp
0
 
MGardnerAuthor Commented:
How I wish the output to appear.
A-B006.bmp
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
als315Commented:
May be you can upload DB with some sample data?
0
 
MGardnerAuthor Commented:
0
 
MGardnerAuthor Commented:
Hey That works a real treat.
0
 
MGardnerAuthor Commented:
sorry meant to accept this as the perfect solution please aWARD POINTS TO THIS SOLUTION
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.