Re: Create query from list in a table
- From: "[MVP] S.Clark" <steve.clark_NOSPAM@xxxxxxxxxx>
- Date: Mon, 13 Feb 2006 09:51:15 -0500
Build a form(page), add a combobox(DDL) for the selection of the Team.
Use the selected value for the queries.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
"Paul Digby" <Paul Digby@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50FD9019-4786-4950-8462-90AB731B900E@xxxxxxxxxxxxxxxx
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;
.
- Follow-Ups:
- Re: Create query from list in a table
- From: Paul Digby
- Re: Create query from list in a table
- References:
- Create query from list in a table
- From: Paul Digby
- Create query from list in a table
- Prev by Date: Re: Non-Equal values in Queries
- Next by Date: Re: Count
- Previous by thread: Create query from list in a table
- Next by thread: Re: Create query from list in a table
- Index(es):
Relevant Pages
|
