• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

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?
0
MGardner
Asked:
MGardner
  • 5
  • 2
1 Solution
 
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
 
als315Commented:
May be you can upload DB with some sample data?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
MGardnerAuthor Commented:
0
 
als315Commented:
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:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now