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?
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.
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
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?
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....
- SELECT RaceEntry.RaceDate, RaceEntry.Distance, RaceEntry.IDNo, Rt_XCLap1Q.RaceNo, Rt_XCLap1Q.Lap1, Rt_XCLap2Q.Lap2, Rt_XCLap3Q.Lap3
-
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)
-
GROUP BY RaceEntry.RaceDate, RaceEntry.Distance, RaceEntry.IDNo, Rt_XCLap1Q.RaceNo, Rt_XCLap1Q.Lap1, Rt_XCLap2Q.Lap2, Rt_XCLap3Q.Lap3
-
ORDER BY Rt_XCLap1Q.Lap1, Rt_XCLap2Q.Lap2, Rt_XCLap3Q.Lap3;
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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:
|
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...
|
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...
|
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...
|
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...
|
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
|
by: chandrashekhar maral |
last post by:
hi all,
can anyone tell me how to add filtering table dynamicaly in DOJO
plssssssssss
|
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...
|
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...
|
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....
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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...
| |