Solved Using a field that was made in a query as criteria for another field in the same query

the_killingbot · Sep 7, 2021

Hi all, I have found some similar posts along these lines but none with a field that was calculated in the same query. By the way, I am making a database on formula e, just so you understand what is going on here.

Here is the SQL code from the query:

SQL:

SELECT
    Max(race_results.r1) AS MaxOf_r1,
    Max(race_results.r2) AS MaxOf_r2,
    Max(race_results.r3) AS MaxOf_r3,
    Max(race_results.r4) AS MaxOf_r4,
    Max(race_results.r5) AS MaxOf_r5,
    Max(race_results.r6) AS MaxOf_r6,
    Max(race_results.r7) AS MaxOf_r7,
    Max(race_results.r8) AS MaxOf_r8,
    Max(race_results.r9) AS MaxOf_r9,
    Max(race_results.r10) AS MaxOf_r10,
    Max(race_results.r11) AS MaxOf_r11
FROM driver INNER JOIN race_results ON driver.ID = race_results.driverID;

I want to add the name of the driver, first_name, and have the criteria whatever the value of MaxOf_r1.
I want to output the winning driver's info if they scored the number of points that appear in the max of the race.
The code for the driver's first name is just: driver.first_name, this would go before MaxOf_r1. I have also attached the access file if that helps.

Thanks.

The_Doc_Man · Sep 7, 2021

You need to clarify your intent. I'm not sure whether you meant what you appear to have said, and that structure suggests you have a normalization violation. Having R1, R2, ..., R10, R11 sounds like you have what is called a repeating group. Did you want the winning driver info only for R1? Because that is what your question appeared to ask.

May I respectfully suggest that you do a search of this forum on "Normalization" or a general web search "Database Normalization" so that you will understand what I mean about database structure.

Once you reset table structure to be normalized, your summation simplifies and so do your options on showing race winners.

the_killingbot · Sep 7, 2021

The_Doc_Man said:
You need to clarify your intent. I'm not sure whether you meant what you appear to have said, and that structure suggests you have a normalization violation. Having R1, R2, ..., R10, R11 sounds like you have what is called a repeating group. Did you want the winning driver info only for R1? Because that is what your question appeared to ask.

Yes, it is, sorry, new to this or of stuff.

MajP · Sep 8, 2021

Since your tables are not correctly constructed you have to use a union query.

Code:

SELECT team.name, driver.first_name, driver.last_name, "Race1" AS Race, race_results.r1 as Result, race_results.r1_note as RaceNote, 1 As Sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race2" AS Race, race_results.r2, race_results.r2_note, 2 as Sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race3" AS Race, race_results.r3, race_results.r3_note, 3 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race4" AS Race, race_results.r4, race_results.r4_note, 4 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race5" AS Race, race_results.r5, race_results.r5_note, 5 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race6" AS Race, race_results.r6, race_results.r6_note, 6 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race7" AS Race, race_results.r7, race_results.r7_note, 7 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race8" AS Race, race_results.r8, race_results.r8_note, 8 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race9" AS Race, race_results.r9, race_results.r9_note, 9 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION
SELECT team.name, driver.first_name, driver.last_name, "Race10" AS Race, race_results.r10, race_results.r10_note, 10 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
UNION SELECT team.name, driver.first_name, driver.last_name, "Race11" AS Race, race_results.r11, race_results.r11_note, 11 as sort
FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID
ORDER BY 7, 5;

That would give results like
qryRaces qryRaces

name	first_name	last_name	Race	Result	RaceNote	Sort
AUDI SPORT ABT SCHAEFFLER	Lucas	Di Grassi	Race1	0		1
AUDI SPORT ABT SCHAEFFLER	Rene	Rast	Race1	0		1
BMW i ANDRETTI MOTORSPORT	Maximilian	Guenther	Race1	0		1
DRAGON/PENSKE AUTOSPORT	Brendon	Hartley	Race1	0		1
DRAGON/PENSKE AUTOSPORT	Nico	Mueller	Race1	0		1
DRAGON/PENSKE AUTOSPORT	Sergio	Sette Camara	Race1	0		1
DS TECHEETACH	Antonio	Felix Da Costa	Race1	0		1
DS TECHEETACH	Jean-Eric	Vergne	Race1	0	DNF	1
MAHINDRA RACING	Alex	Lynn	Race1	0		1
MAHINDRA RACING	Pascal	Wehrlein	Race1	0		1
NIO 333 FORMULA E TEAM	Daniel	Abt	Race1	0	DNF	1
NIO 333 FORMULA E TEAM	Ma	Qing hua	Race1	0		1
NIO 333 FORMULA E TEAM	Oliver	Turvey	Race1	0		1
NISSIAN E.DAMS	Sebastien	Buemi	Race1	0	DNF	1
PANASONIC JAGUAR RACING	James	Calado	Race1	0		1
PANASONIC JAGUAR RACING	Tom	Blomqvist	Race1	0		1
ROKiT VENTURI RACING	Felipe	Massa	Race1	0		1
TAG HEUER PORSCHE FORMULA E	Neel	Jani	Race1	0		1
MAHINDRA RACING	Jerome	D'Ambrosio	Race1	2		1
PANASONIC JAGUAR RACING	Mitch	Evans	Race1	2	FL	1
ROKiT VENTURI RACING	Edoardo	Mortara	Race1	6		1
BMW i ANDRETTI MOTORSPORT	Alexander	Sims	Race1	7	P	1
MERCEDES-EQ FORMULA E TEAM	Nyck	De Vries	Race1	8		1
ENVISION VIRGIN RACING	Robin	Frijns	Race1	10		1
NISSIAN E.DAMS	Oliver	Rowland	Race1	12		1
MERCEDES-EQ FORMULA E TEAM	Stoffel	Vandoorne	Race1	15		1
TAG HEUER PORSCHE FORMULA E	Andre	Lotterer	Race1	18		1
ENVISION VIRGIN RACING	Sam	Bird	Race1	26		1
AUDI SPORT ABT SCHAEFFLER	Rene	Rast	Race2	0		2
BMW i ANDRETTI MOTORSPORT	Maximilian	Guenther	Race2	0		2
DRAGON/PENSKE AUTOSPORT	Nico	Mueller	Race2	0	DNF	2
DRAGON/PENSKE AUTOSPORT	Sergio	Sette Camara	Race2	0		2
ENVISION VIRGIN RACING	Robin	Frijns	Race2	0	DNF	2
ENVISION VIRGIN RACING	Sam	Bird	Race2	0	DNF	2
MAHINDRA RACING	Alex	Lynn	Race2	0		2
MAHINDRA RACING	Jerome	D'Ambrosio	Race2	0	DNF	2
MAHINDRA RACING	Pascal	Wehrlein	Race2	0		2
MERCEDES-EQ FORMULA E TEAM	Nyck	De Vries	Race2	0		2

from there you can get the max of each race

Code:

SELECT qryRaces.Race, Max(qryRaces.Result) AS MaxOfResult, qryRaces.Sort
FROM qryRaces
GROUP BY qryRaces.Race, qryRaces.Sort
ORDER BY qryRaces.Sort;

MajP · Sep 8, 2021

qryMaxResults qryMaxResults

Race	MaxOfResult	Sort
Race1	26	1
Race2	28	2
Race3	25	3
Race4	26	4
Race5	28	5
Race6	30	6
Race7	28	7
Race8	25	8
Race9	29	9
Race10	29	10
Race11	28	11

Then get who had max results

Code:

SELECT qryRaces.name, qryRaces.first_name, qryRaces.last_name, qryRaces.Race, qryRaces.Result, qryRaces.RaceNote, qryRaces.Sort
FROM qryRaces INNER JOIN qryMaxResults ON (qryRaces.Race = qryMaxResults.Race) AND (qryRaces.Result = qryMaxResults.MaxOfResult)
ORDER BY qryRaces.Sort;

Query3 Query3

name	first_name	last_name	Race	Result	RaceNote	Sort
ENVISION VIRGIN RACING	Sam	Bird	Race1	26		1
BMW i ANDRETTI MOTORSPORT	Alexander	Sims	Race2	28	P	2
BMW i ANDRETTI MOTORSPORT	Maximilian	Guenther	Race3	25		3
PANASONIC JAGUAR RACING	Mitch	Evans	Race4	26		4
DS TECHEETACH	Antonio	Felix Da Costa	Race5	28	P	5
DS TECHEETACH	Antonio	Felix Da Costa	Race6	30	P & FL	6
DS TECHEETACH	Antonio	Felix Da Costa	Race7	28	P	7
BMW i ANDRETTI MOTORSPORT	Maximilian	Guenther	Race8	25		8
DS TECHEETACH	Jean-Eric	Vergne	Race9	29	P	9
NISSIAN E.DAMS	Oliver	Rowland	Race10	29	P & FL	10
MERCEDES-EQ FORMULA E TEAM	Stoffel	Vandoorne	Race11	28	P	11

MajP · Sep 8, 2021

If you design your tables correctly this will be much easier.
Your table should be
tblRaceResults

Code:

DriverID_FK (do not need team id since that comes from the driver table)
RaceID_FK (a foreign key to a race table)
RaceResult
DriverRace_Note

You should not have a column for each race.

the_killingbot · Sep 8, 2021

MajP said:
Since your tables are not correctly constructed you have to use a union query.

Code:

SELECT team.name, driver.first_name, driver.last_name, "Race1" AS Race, race_results.r1 as Result, race_results.r1_note as RaceNote, 1 As Sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race2" AS Race, race_results.r2, race_results.r2_note, 2 as Sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race3" AS Race, race_results.r3, race_results.r3_note, 3 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race4" AS Race, race_results.r4, race_results.r4_note, 4 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race5" AS Race, race_results.r5, race_results.r5_note, 5 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race6" AS Race, race_results.r6, race_results.r6_note, 6 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race7" AS Race, race_results.r7, race_results.r7_note, 7 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race8" AS Race, race_results.r8, race_results.r8_note, 8 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race9" AS Race, race_results.r9, race_results.r9_note, 9 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race10" AS Race, race_results.r10, race_results.r10_note, 10 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID UNION SELECT team.name, driver.first_name, driver.last_name, "Race11" AS Race, race_results.r11, race_results.r11_note, 11 as sort FROM team INNER JOIN (driver INNER JOIN race_results ON driver.ID = race_results.driverID) ON team.ID = driver.teamID ORDER BY 7, 5;

That would give results like
qryRaces qryRaces

name first_name last_name Race Result RaceNote Sort
AUDI SPORT ABT SCHAEFFLER Lucas Di Grassi Race1
0
1
AUDI SPORT ABT SCHAEFFLER Rene Rast Race1
0
1
BMW i ANDRETTI MOTORSPORT Maximilian Guenther Race1
0
1
DRAGON/PENSKE AUTOSPORT Brendon Hartley Race1
0
1
DRAGON/PENSKE AUTOSPORT Nico Mueller Race1
0
1
DRAGON/PENSKE AUTOSPORT Sergio Sette Camara Race1
0
1
DS TECHEETACH Antonio Felix Da Costa Race1
0
1
DS TECHEETACH Jean-Eric Vergne Race1
0
DNF
1
MAHINDRA RACING Alex Lynn Race1
0
1
MAHINDRA RACING Pascal Wehrlein Race1
0
1
NIO 333 FORMULA E TEAM Daniel Abt Race1
0
DNF
1
NIO 333 FORMULA E TEAM Ma Qing hua Race1
0
1
NIO 333 FORMULA E TEAM Oliver Turvey Race1
0
1
NISSIAN E.DAMS Sebastien Buemi Race1
0
DNF
1
PANASONIC JAGUAR RACING James Calado Race1
0
1
PANASONIC JAGUAR RACING Tom Blomqvist Race1
0
1
ROKiT VENTURI RACING Felipe Massa Race1
0
1
TAG HEUER PORSCHE FORMULA E Neel Jani Race1
0
1
MAHINDRA RACING Jerome D'Ambrosio Race1
2
1
PANASONIC JAGUAR RACING Mitch Evans Race1
2
FL
1
ROKiT VENTURI RACING Edoardo Mortara Race1
6
1
BMW i ANDRETTI MOTORSPORT Alexander Sims Race1
7
P
1
MERCEDES-EQ FORMULA E TEAM Nyck De Vries Race1
8
1
ENVISION VIRGIN RACING Robin Frijns Race1
10
1
NISSIAN E.DAMS Oliver Rowland Race1
12
1
MERCEDES-EQ FORMULA E TEAM Stoffel Vandoorne Race1
15
1
TAG HEUER PORSCHE FORMULA E Andre Lotterer Race1
18
1
ENVISION VIRGIN RACING Sam Bird Race1
26
1
AUDI SPORT ABT SCHAEFFLER Rene Rast Race2
0
2
BMW i ANDRETTI MOTORSPORT Maximilian Guenther Race2
0
2
DRAGON/PENSKE AUTOSPORT Nico Mueller Race2
0
DNF
2
DRAGON/PENSKE AUTOSPORT Sergio Sette Camara Race2
0
2
ENVISION VIRGIN RACING Robin Frijns Race2
0
DNF
2
ENVISION VIRGIN RACING Sam Bird Race2
0
DNF
2
MAHINDRA RACING Alex Lynn Race2
0
2
MAHINDRA RACING Jerome D'Ambrosio Race2
0
DNF
2
MAHINDRA RACING Pascal Wehrlein Race2
0
2
MERCEDES-EQ FORMULA E TEAM Nyck De Vries Race2
0
2

from there you can get the max of each race

Code:

SELECT qryRaces.Race, Max(qryRaces.Result) AS MaxOfResult, qryRaces.Sort FROM qryRaces GROUP BY qryRaces.Race, qryRaces.Sort ORDER BY qryRaces.Sort;

When I ran the union code I got an error saying: "Type mismatch in expression"
Also, I am a bit of a novice programmer when it comes to SQL, so your code is quite confusing, I am really grateful for all your help though, I'll spend a bit more time reading over it to understand it.
One more thing, would you be able to edit the file that I attached at the top and redesign the tables, just to give me a visual idea of what I should change.

MajP · Sep 8, 2021

When I ran the union code I got an error saying: "Type mismatch in expression"

I forgot to mention that. Your TeamID in the Driver table is ShortText. It needs to be numeric. You cannot join two different datatypes.

mike60smart · Sep 8, 2021

the_killingbot said:
When I ran the union code I got an error saying: "Type mismatch in expression"
Also, I am a bit of a novice programmer when it comes to SQL, so your code is quite confusing, I am really grateful for all your help though, I'll spend a bit more time reading over it to understand it.
One more thing, would you be able to edit the file that I attached at the top and redesign the tables, just to give me a visual idea of what I should change

Hi

Your table structure should be as shown in the attached.

The form that opens at startup allows you to select a Driver and then add details of races

I entered data for Driver 1 Only

MajP · Sep 8, 2021

Since @mike60smart was so kind, I did the next step and used the union query to update his correctly structures table with all your data. If you use this structure things will be far easier to query and work with.

the_killingbot · Sep 8, 2021

Thank you both so much this has been a huge help.

Solved Using a field that was made in a query as criteria for another field in the same query

the_killingbot

New member

Attachments

The_Doc_Man

Immoderate Moderator

the_killingbot

New member

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

the_killingbot

New member

MajP

You've got your good things, and you've got mine.

Attachments

mike60smart

Registered User.

Attachments

MajP

You've got your good things, and you've got mine.

Attachments

the_killingbot

New member

Similar threads

Users who are viewing this thread