By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,784 Members | 3,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,784 IT Pros & Developers. It's quick & easy.

Sorting/filtering a table with a Query to change layout

100+
P: 547
I have a table called RaceEntry2 and need to use a query to change the layout of the data.
Its currently in this format
RaceName =numberfield; racedate=datefield;lap1-8=datefields; RaceNo=numberfield
This is how data was actually captured in subform
Racename-----RaceNo------Lap1-----Lap2-----lap3
--A------------1-------------12:15-----------------
--A------------2-------------12:45-----------------
--A------------3-------------12:50-----------------
--A------------2----------------------24:28----------
--A------------1----------------------24:45----------
--A------------3----------------------24:58----------
--A------------2---------------------------------35:12
--A------------1---------------------------------35:30
--A------------3---------------------------------35:59

I would like it to be sorted/filtered in this format using a Query

Racename-----RaceNo------Lap1-----Lap2-----lap3
--A------------1-------------12:15---24:45----35:30
--A------------2-------------12:45---24:28----35:12
--A------------3-------------12:50---24:58----35:59

any suggestions pls?
Nov 25 '10 #1
Share this Question
Share on Google+
6 Replies


P: 79
Try setting query as follows.
fields Race, Racenum, Racedate, Lap1, Lap2, Lap3
Right click any field column and select totals so that sort shows on left row title. Set all totals to Group By and set sort on race and racedate to ascending.
Nov 25 '10 #2

100+
P: 547
Normal sorting/grouping does not work.
get the following if i use totals, group and ascending sort in query.
I would like to have all raceno 1's data in one row, raceno=2 in one row etc

Attached Images
File Type: jpg sorting1.jpg (43.8 KB, 410 views)
Nov 25 '10 #3

100+
P: 547
i get it like this but i then have to sort each lap field individually,and then link it with another table. complicated process!
see pics.
is there no shorter way of doing it?


Attached Images
File Type: jpg sorting2.jpg (37.9 KB, 392 views)
File Type: jpg sorting3.jpg (32.2 KB, 342 views)
Nov 25 '10 #4

100+
P: 255
Can you copy the codes from the SQL view and paste it up here? As currently I don't think there's a better work around to your current solution....
Nov 26 '10 #5

100+
P: 547
Expand|Select|Wrap|Line Numbers
  1. SELECT RaceEntry.RaceDate, RaceEntry.Distance, RaceEntry.IDNo, Rt_XCLap1Q.RaceNo, Rt_XCLap1Q.Lap1, Rt_XCLap2Q.Lap2, Rt_XCLap3Q.Lap3
  2. FROM ((RaceEntry INNER JOIN Rt_XCLap1Q ON (RaceEntry.RaceDate = Rt_XCLap1Q.RaceDate) AND (RaceEntry.RaceNo = Rt_XCLap1Q.RaceNo)) INNER JOIN Rt_XCLap2Q ON (RaceEntry.RaceDate = Rt_XCLap2Q.RaceDate) AND (RaceEntry.RaceNo = Rt_XCLap2Q.RaceNo)) INNER JOIN Rt_XCLap3Q ON (RaceEntry.RaceDate = Rt_XCLap3Q.RaceDate) AND (RaceEntry.RaceNo = Rt_XCLap3Q.RaceNo)
  3. GROUP BY RaceEntry.RaceDate, RaceEntry.Distance, RaceEntry.IDNo, Rt_XCLap1Q.RaceNo, Rt_XCLap1Q.Lap1, Rt_XCLap2Q.Lap2, Rt_XCLap3Q.Lap3
  4. ORDER BY Rt_XCLap1Q.Lap1, Rt_XCLap2Q.Lap2, Rt_XCLap3Q.Lap3;
  5.  
Nov 26 '10 #6

dsatino
100+
P: 393
What you really need is a normalized, relational backend table structure. From what I've seen of what you've got, that would mean starting over. But it would make creating the type of outputs you want so much easier.
Nov 26 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.