Create query from list in a table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I undertsand the process of what I need, but do not know how to get there. I
will try and describe what I have so far.

There are 15 sub queries that individully produce the information that I
need. I also have one query that uses INNER JOIN to put all that data into a
table. However, each of the sub queries uses @Team requesting manual entry
and I have to do this 26 times, having to save the table each time with a
different name.

What I would like to do is, run the one query, selecting the team name from
a list in a table (in place of the @Team) and outputting to the one table,
adding the name of that Team in a colum so that this can be easily refenced
using asp. I have created the table ready to receive data

Knowing what I want, the above reads fine, but I guess your interpretation
may be different, so am expecting a few questions. I have copied the main
INNER JOIN query below


INSERT INTO F1_Team_Master ( Country, Race, Wins, Podia, Finishes, Laps,
DNS, DNF, DQ, [First], FirstRace, [Last], LastRace, FirstWinRace,
FirstWinYear, FirstPodRace, FirstPodYear, LastWinRace, LastWinYear,
LastPodRace, LastPodYear )
SELECT Query101a.Country, Query105a.Race, Query101a.Wins, Query102a.Podia,
Query103a.Finishes, Query104a.Laps, Query106a.DNS, Query107a.DNF,
Query108a.DQ, Query109a.First, Query110a.FirstRace, Query109a.Last,
Query110a.LastRace, Query111a.FirstWinRace, Query111a.FirstWinYear,
Query112a.FirstPodRace, Query112a.FirstPodYear, Query113a.LastWinRace,
Query113a.LastWinYear, Query114a.LastPodRace, Query114a.LastPodYear
FROM (((((((((Query101a INNER JOIN Query102a ON Query101a.Country =
Query102a.Country) INNER JOIN Query103a ON Query102a.Country =
Query103a.Country) INNER JOIN Query104a ON Query103a.Country =
Query104a.Country) INNER JOIN Query105a ON Query104a.Country =
Query105a.Country) INNER JOIN (((Query106a INNER JOIN Query107a ON
Query106a.Country = Query107a.Country) INNER JOIN Query108a ON
Query107a.Country = Query108a.Country) INNER JOIN Query109a ON
Query108a.Country = Query109a.Country) ON Query105a.Country =
Query106a.Country) INNER JOIN Query110a ON Query109a.Country =
Query110a.Country) INNER JOIN Query111a ON Query110a.Country =
Query111a.Country) INNER JOIN Query112a ON Query111a.Country =
Query112a.Country) INNER JOIN Query113a ON Query112a.Country =
Query113a.Country) INNER JOIN Query114a ON Query113a.Country =
Query114a.Country
ORDER BY Query101a.Country;



Query101a
SELECT F1_Countrys.Country, Query101.TotalWins AS Wins
FROM F1_Countrys LEFT JOIN Query101 ON F1_Countrys.Country=Query101.Country
ORDER BY F1_Countrys.Country;



Query101
SELECT F1_Results.Country, [Team], Count(F1_Results.Team) AS TotalWins
FROM F1_Results
WHERE (((F1_Results.Pos)='1') And ((F1_Results.Team)=[@Team]))
GROUP BY F1_Results.Country, [Team]
ORDER BY F1_Results.Country;

.

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)
  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ...
    (microsoft.public.access.queries)