Order By in Transform Crosstab Query

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
LVL 2
MGardnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill RossCommented:
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
MGardnerAuthor Commented:
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
Bill RossCommented:
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
MGardnerAuthor Commented:
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
Bill RossCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.