473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Sorting/filtering a table with a Query to change layout

547 512MB
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
6 1978
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
neelsfer
547 512MB
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, 450 views)
Nov 25 '10 #3
neelsfer
547 512MB
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, 447 views)
File Type: jpg sorting3.jpg (32.2 KB, 387 views)
Nov 25 '10 #4
colintis
255 100+
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
neelsfer
547 512MB
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
393 256MB
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

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

Similar topics

2
by: Heist | last post by:
Hi, I just want to know to turn this: CREATE TABLE . ( NOT NULL , (50) COLLATE French_CI_AS NULL , NOT NULL , (50) COLLATE French_CI_AS NOT NULL , NULL , NULL ) ON into this:
0
by: Gianfranco | last post by:
Hi I got a problem with 2 tables. I have a table, say A, with x records, coming from a make table query and a table, say B, with y records, coming from another make table query. I need to join...
4
by: Oreo Bomb | last post by:
I have a secured database that contains a Read-Only group. This group has permissions to view reports, but cannot add, edit, or delete any DB objects. One of the reports the group needs access to...
3
by: Hanif Merali | last post by:
Hello, I'm having some difficulties creating a make table query. The source table which I'm basing the make table query has the fields: CIF BusLine1-2001 BusLine1-2002 BusLine1-2003...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
1
by: GCM | last post by:
Hi, Thanks to everyone for helping on the last problem I had. Does the Make Table Query exist in Access 2000? If so, how do you access it? Thanks, GCM
1
by: chandrashekhar maral | last post by:
hi all, can anyone tell me how to add filtering table dynamicaly in DOJO plssssssssss
5
by: vegak18 | last post by:
Dear Experts, I was was wondering if there was some way to specify output properties of a make table query. Specifically, I am haivng a problem with numbers being specified as text. while I...
2
by: mfsforums | last post by:
Hi, Working with a database that's new to me and they have a make table query that creates a variety of fields, a handful of which are designated as datatype 'Number' to start. It is then...
2
by: Laurel | last post by:
I am completely new to access. I have a make-table query that combines data from two/more tables, asks for a criteria, deletes the old table and produces a new table every time under the same name....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.