Create query from list in a table

P

Paul Digby

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;
 
M

[MVP] S.Clark

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 said:
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;
Click to expand...
 
P

Paul Digby

That looks god for a manual operation, but I would prefer to have it
automated so that it selects one after the other fro me

[MVP] S.Clark said:
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 said:
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;
Click to expand...
Click to expand...
 
A

Amy Blankenship

Paul Digby said:
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
Click to expand...

I use ASP all the time, and I've never felt the need to create a separate
table to access the data. Why not just make one query that groups by team
and then loop through in the ASP and do whatever based on which team? Or
better yet, use a SHAPE query?

What is the reason you think you need to make a table to get at this data?
 
P

Paul Digby

My experience is not this advance and am looking at any alternative to have
automatic operation to retrieve and display the information

'Shape' means absolutlely nothing to me, perhaps you could look at my
example and start me off?
 
A

Amy Blankenship

If you're not that experienced, a SHAPE query may be a bit difficult to get
your head around
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00a1.asp.

You don't really say what you're using most of the subqueries for, so it's
impossible to distill it down to something more recognizable. But you
should be able to have a base query SELECT TeamID from Teams WHERE
Teams.TeamName IN ('Team A', 'Team B', 'Team etc.').

Then if you join all queries on that query, you should return only
information from those teams. You can execute queries from ASP. I can't
find a reference, but I do it like this:

"Exec [queryName] '" & strParam & "', " & NumericParam

HTH;

Amy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format the result field in a make table query 1
getting fewer records from a union query than in individual table 5
Delete query based on criteria in a linked table 5
Sumarize data in Query like in a Pivot Table 7
With a Query in Access 2007, How can I Create This Query 3
insert a crosstab in to temp table query 2
Update a table from a query 7
Multiple supervisors over a date range in query 1

Top