Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order By in Transform Crosstab Query

Posted on 2013-11-16
5
Medium Priority
?
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

609 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