Solved Using a field that was made in a query as criteria for another field in the same query (1 Viewer)

the_killingbot

New member
Local time
Today, 20:14
Joined
Sep 7, 2021
Messages
4
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.
 

Attachments

  • formula-e_S6.accdb
    3 MB · Views: 255
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 28, 2001
Messages
27,001
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

New member
Local time
Today, 20:14
Joined
Sep 7, 2021
Messages
4
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

You've got your good things, and you've got mine.
Local time
Today, 16:14
Joined
May 21, 2018
Messages
8,463
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

namefirst_namelast_nameRaceResultRaceNoteSort
AUDI SPORT ABT SCHAEFFLERLucasDi GrassiRace1
0​
1​
AUDI SPORT ABT SCHAEFFLERReneRastRace1
0​
1​
BMW i ANDRETTI MOTORSPORTMaximilianGuentherRace1
0​
1​
DRAGON/PENSKE AUTOSPORTBrendonHartleyRace1
0​
1​
DRAGON/PENSKE AUTOSPORTNicoMuellerRace1
0​
1​
DRAGON/PENSKE AUTOSPORTSergioSette CamaraRace1
0​
1​
DS TECHEETACHAntonioFelix Da CostaRace1
0​
1​
DS TECHEETACHJean-EricVergneRace1
0​
DNF
1​
MAHINDRA RACINGAlexLynnRace1
0​
1​
MAHINDRA RACINGPascalWehrleinRace1
0​
1​
NIO 333 FORMULA E TEAMDanielAbtRace1
0​
DNF
1​
NIO 333 FORMULA E TEAMMaQing huaRace1
0​
1​
NIO 333 FORMULA E TEAMOliverTurveyRace1
0​
1​
NISSIAN E.DAMSSebastienBuemiRace1
0​
DNF
1​
PANASONIC JAGUAR RACINGJamesCaladoRace1
0​
1​
PANASONIC JAGUAR RACINGTomBlomqvistRace1
0​
1​
ROKiT VENTURI RACINGFelipeMassaRace1
0​
1​
TAG HEUER PORSCHE FORMULA ENeelJaniRace1
0​
1​
MAHINDRA RACINGJeromeD'AmbrosioRace1
2​
1​
PANASONIC JAGUAR RACINGMitchEvansRace1
2​
FL
1​
ROKiT VENTURI RACINGEdoardoMortaraRace1
6​
1​
BMW i ANDRETTI MOTORSPORTAlexanderSimsRace1
7​
P
1​
MERCEDES-EQ FORMULA E TEAMNyckDe VriesRace1
8​
1​
ENVISION VIRGIN RACINGRobinFrijnsRace1
10​
1​
NISSIAN E.DAMSOliverRowlandRace1
12​
1​
MERCEDES-EQ FORMULA E TEAMStoffelVandoorneRace1
15​
1​
TAG HEUER PORSCHE FORMULA EAndreLottererRace1
18​
1​
ENVISION VIRGIN RACINGSamBirdRace1
26​
1​
AUDI SPORT ABT SCHAEFFLERReneRastRace2
0​
2​
BMW i ANDRETTI MOTORSPORTMaximilianGuentherRace2
0​
2​
DRAGON/PENSKE AUTOSPORTNicoMuellerRace2
0​
DNF
2​
DRAGON/PENSKE AUTOSPORTSergioSette CamaraRace2
0​
2​
ENVISION VIRGIN RACINGRobinFrijnsRace2
0​
DNF
2​
ENVISION VIRGIN RACINGSamBirdRace2
0​
DNF
2​
MAHINDRA RACINGAlexLynnRace2
0​
2​
MAHINDRA RACINGJeromeD'AmbrosioRace2
0​
DNF
2​
MAHINDRA RACINGPascalWehrleinRace2
0​
2​
MERCEDES-EQ FORMULA E TEAMNyckDe VriesRace2
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

You've got your good things, and you've got mine.
Local time
Today, 16:14
Joined
May 21, 2018
Messages
8,463
qryMaxResults qryMaxResults

RaceMaxOfResultSort
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

namefirst_namelast_nameRaceResultRaceNoteSort
ENVISION VIRGIN RACINGSamBirdRace1
26​
1​
BMW i ANDRETTI MOTORSPORTAlexanderSimsRace2
28​
P
2​
BMW i ANDRETTI MOTORSPORTMaximilianGuentherRace3
25​
3​
PANASONIC JAGUAR RACINGMitchEvansRace4
26​
4​
DS TECHEETACHAntonioFelix Da CostaRace5
28​
P
5​
DS TECHEETACHAntonioFelix Da CostaRace6
30​
P & FL
6​
DS TECHEETACHAntonioFelix Da CostaRace7
28​
P
7​
BMW i ANDRETTI MOTORSPORTMaximilianGuentherRace8
25​
8​
DS TECHEETACHJean-EricVergneRace9
29​
P
9​
NISSIAN E.DAMSOliverRowlandRace10
29​
P & FL
10​
MERCEDES-EQ FORMULA E TEAMStoffelVandoorneRace11
28​
P
11​
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:14
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 20:14
Joined
Sep 7, 2021
Messages
4
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

namefirst_namelast_nameRaceResultRaceNoteSort
AUDI SPORT ABT SCHAEFFLERLucasDi GrassiRace1
0​
1​
AUDI SPORT ABT SCHAEFFLERReneRastRace1
0​
1​
BMW i ANDRETTI MOTORSPORTMaximilianGuentherRace1
0​
1​
DRAGON/PENSKE AUTOSPORTBrendonHartleyRace1
0​
1​
DRAGON/PENSKE AUTOSPORTNicoMuellerRace1
0​
1​
DRAGON/PENSKE AUTOSPORTSergioSette CamaraRace1
0​
1​
DS TECHEETACHAntonioFelix Da CostaRace1
0​
1​
DS TECHEETACHJean-EricVergneRace1
0​
DNF
1​
MAHINDRA RACINGAlexLynnRace1
0​
1​
MAHINDRA RACINGPascalWehrleinRace1
0​
1​
NIO 333 FORMULA E TEAMDanielAbtRace1
0​
DNF
1​
NIO 333 FORMULA E TEAMMaQing huaRace1
0​
1​
NIO 333 FORMULA E TEAMOliverTurveyRace1
0​
1​
NISSIAN E.DAMSSebastienBuemiRace1
0​
DNF
1​
PANASONIC JAGUAR RACINGJamesCaladoRace1
0​
1​
PANASONIC JAGUAR RACINGTomBlomqvistRace1
0​
1​
ROKiT VENTURI RACINGFelipeMassaRace1
0​
1​
TAG HEUER PORSCHE FORMULA ENeelJaniRace1
0​
1​
MAHINDRA RACINGJeromeD'AmbrosioRace1
2​
1​
PANASONIC JAGUAR RACINGMitchEvansRace1
2​
FL
1​
ROKiT VENTURI RACINGEdoardoMortaraRace1
6​
1​
BMW i ANDRETTI MOTORSPORTAlexanderSimsRace1
7​
P
1​
MERCEDES-EQ FORMULA E TEAMNyckDe VriesRace1
8​
1​
ENVISION VIRGIN RACINGRobinFrijnsRace1
10​
1​
NISSIAN E.DAMSOliverRowlandRace1
12​
1​
MERCEDES-EQ FORMULA E TEAMStoffelVandoorneRace1
15​
1​
TAG HEUER PORSCHE FORMULA EAndreLottererRace1
18​
1​
ENVISION VIRGIN RACINGSamBirdRace1
26​
1​
AUDI SPORT ABT SCHAEFFLERReneRastRace2
0​
2​
BMW i ANDRETTI MOTORSPORTMaximilianGuentherRace2
0​
2​
DRAGON/PENSKE AUTOSPORTNicoMuellerRace2
0​
DNF
2​
DRAGON/PENSKE AUTOSPORTSergioSette CamaraRace2
0​
2​
ENVISION VIRGIN RACINGRobinFrijnsRace2
0​
DNF
2​
ENVISION VIRGIN RACINGSamBirdRace2
0​
DNF
2​
MAHINDRA RACINGAlexLynnRace2
0​
2​
MAHINDRA RACINGJeromeD'AmbrosioRace2
0​
DNF
2​
MAHINDRA RACINGPascalWehrleinRace2
0​
2​
MERCEDES-EQ FORMULA E TEAMNyckDe VriesRace2
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

You've got your good things, and you've got mine.
Local time
Today, 16:14
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • formula-e_S6.accdb
    3.4 MB · Views: 246

mike60smart

Registered User.
Local time
Today, 20:14
Joined
Aug 6, 2017
Messages
1,899
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
 

Attachments

  • formula-e_S6.accdb
    1.8 MB · Views: 260

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:14
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • formula-e_S6 V2.accdb
    1.5 MB · Views: 272

Users who are viewing this thread

Top Bottom