Solved

Order By in Transform Crosstab Query

Posted on 2013-11-16
5
325 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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: …

743 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

12 Experts available now in Live!

Get 1:1 Help Now