Solved

Order By in Transform Crosstab Query

Posted on 2013-11-16
5
352 Views
Last Modified: 2014-05-06
I have a crosstab query in a competition database that groups all images next to a member orders the list rows by Member ID where I need the row order to be ascending on Members Last Name.

I will need to add a table to the design view I am guessing but cannot get it to reference and sort on the last name

My Original SQL is

TRANSFORM Sum(tblEntrants.Points) AS SumOfPoints
SELECT tblEntrants.MemberID, tblEntrants.CID, Sum(tblEntrants.Points) AS TotalPoints
FROM tblEntrants
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");

And I tried some variations as in below but keep getting errors

TRANSFORM Sum(tblEntrants.Points) AS SumOfPoints
SELECT tblEntrants.MemberID, tblEntrants.CID, Sum(tblEntrants.Points) AS TotalPoints
FROM tblEntrants, tblMembers
GROUP BY tblEntrants.MemberID, tblEntrants.CID, tblMembers.[Last Name]
ORDER BY tblMembers.[Last Name]
PIVOT "Image" & DCount("*","tblEntrants","[CEID]<=" & [CEID] & " AND [CID] = " & [CID] & " AND [MemberID] = " & [MemberID]) In ("Image1","Image2","Image3");

TblMembers

MID PK
First Name
Last Name

Any help on getting this to work would be most appreciated
0
Comment
Question by:MGardner
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39653224
Hi,

I would save the transform query with the data displayed as you want with correct rows and columns, etc.  I would then create a second query based on the transform query that sorts the way you want and run that.  In the second query you can also rename the column headings if you want.  Essentially, use one query to prepare the data and the second to present it correctly.

Easy to troubleshoot.

Regards,

Bill
0
 
LVL 2

Author Comment

by:MGardner
ID: 39653408
I have tried that but now it has dropped the sub form out. I has stripped out the Db for you to look at so you can get a better idea of what I am trying to achieve in the report.

if you open the form sfrmCompetitions and click the cmd button for Blank Results Sheet the report will com up with each entry of which there are three entries per person 21 altogether, all interpersed evenly i.e. image 1 for each entrant then Image 2 for each entrant etc.

at the bottom of the report is the part I am trying to sort properly in that the order of the entrants should be the same as (in the case of this report) the first 7 of the main report.

Hope this sound not to cranky

with thanks
Slimed-Competion-Db.zip
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39653490
Hi,

The issue is that you are sorting the parent based on CEID which is not available in the subreport.  Technically you should have a Sort as well as a Grouping on the parent.

There is no sort or grouping on the subreport.

There are duplicate entries for say "Colin Bird" on the parent.

I would suggest you add a MemberID sort to both the parent and the subreport.  That way both reports are always sorted the same.

Regards,

Bill
0
 
LVL 2

Author Comment

by:MGardner
ID: 39653528
Hi Bill, I have managed to get both to sort by using the following on the subreport

TRANSFORM Sum(tblEntrants.Points) AS SumOfPoints
SELECT tblEntrants.MemberID, tblEntrants.CID, Sum(tblEntrants.Points) AS TotalPoints
FROM tblEntrants INNER JOIN tblMembers ON tblEntrants.MemberID = tblMembers.ID
GROUP BY tblEntrants.MemberID, tblEntrants.CID, tblMembers.[Last Name]
ORDER BY tblMembers.[Last Name]
PIVOT "Image" & DCount("*","tblEntrants","[CEID]<=" & [CEID] & " AND [CID] = " & [CID] & " AND [MemberID] = " & [MemberID]) In ("Image1","Image2","Image3");

hiding the column [Last Name]
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 39653591
Hi,

In a report the query sort does not matter.  You should set Sort on the reports specifically for each one using the Sort and Group options.

Regards,

Bill
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now