Solved

Order By in Transform Crosstab Query

Posted on 2013-11-16
5
371 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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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