union query problem

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Hi I have a table like this

ID Date Player Home Result Runout Player1 Away Result1
Runout1

and my union query is as follws:

SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
Sum(IIf([Result]="w",1,0)) AS Won, Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Sum(IIf([Result1]="w",1,0)) AS Won, Sum(IIf([Result1]="l",
1,0)) AS Lost, Sum(IIf([Runout1]="x",1,0)) AS Runouts
FROM tblResults2;


the problem is i get this error

"you tried to execute a query that does not include the specified
expression 'playername' as part of the aggreagate function"

if i do either one of these as a select query, and put in group by
PlayerName, they work.

How can i get the union query to work?
 
Hi I have a table like this

ID Date Player Home Result Runout Player1 Away Result1
Runout1

and my union query is as follws:

SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
Sum(IIf([Result]="w",1,0)) AS Won, Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Sum(IIf([Result1]="w",1,0)) AS Won, Sum(IIf([Result1]="l",
1,0)) AS Lost, Sum(IIf([Runout1]="x",1,0)) AS Runouts
FROM tblResults2;


the problem is i get this error

"you tried to execute a query that does not include the specified
expression 'playername' as part of the aggreagate function"

if i do either one of these as a select query, and put in group by
PlayerName, they work.

How can i get the union query to work?
Click to expand...

By including the group-by in each of the SELECTs in the query.

You're using totals queries - the Sum operators - so if you want to include
the player, you need to group by it; or if you want the sum for the team,
summing all the players, you need to leave the Player fields out of the UNION
altogether.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Hi I have a table like this
Click to expand...
ID    Date  Player  Home  Result  Runout  Player1  Away  Result1
Runout1
Click to expand...
and my union query is as follws:
Click to expand...
SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
Sum(IIf([Result]="w",1,0)) AS Won, Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Sum(IIf([Result1]="w",1,0)) AS Won, Sum(IIf([Result1]="l",
1,0)) AS Lost, Sum(IIf([Runout1]="x",1,0)) AS Runouts
FROM tblResults2;
Click to expand...
the problem is i get this error
Click to expand...
"you tried to execute a query  that does not include the specified
expression 'playername' as part of the aggreagate function"
Click to expand...
if i do either one of these as a select query, and put in group by
PlayerName, they work.
Click to expand...
How can i get the union query to work?
Click to expand...

By including the group-by in each of the SELECTs in the query.

You're using totals queries - the Sum operators - so if you want to include
the player, you need to group by it; or if you want the sum for the team,
summing all the players, you need to leave the Player fields out of the UNION
altogether.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...


I tried what you said and i still get them same message

SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
Sum(IIf([Result]="w",1,0)) AS Won, Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM tblResults2
GROUP BY PlayerName, Team
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Sum(IIf([Result1]="w",1,0)) AS Won, Sum(IIf([Result1]="l",
1,0)) AS Lost, Sum(IIf([Runout1]="x",1,0)) AS Runouts
FROM tblResults2
GROUP BY PlayerName, Team;

what am i doing wrong?
 
what am i doing wrong?
Click to expand...

Hrm. Since you will want to calculate the sums over BOTH queries in the UNION,
you'll probably have to do the UNION just of the raw data, and do the sums in
a separate query based on the UNION query. Try

SELECT PlayerName, Team, Sum(IIf([Result]="w",1,0)) AS Won,
Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM
(SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team, Result,
Runout
FROM tblResults2
UNION ALL
SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Result, Runout1
FROM tblResults2);

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
what am i doing wrong?
Click to expand...

Hrm. Since you will want to calculate the sums over BOTH queries in the UNION,
you'll probably have to do the UNION  just of the raw data, and do the sums in
a separate query based on the UNION query. Try

SELECT PlayerName, Team, Sum(IIf([Result]="w",1,0)) AS Won,
Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM
(SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team, Result,
Runout
FROM tblResults2
UNION ALL
SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Result, Runout1
FROM tblResults2);

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com
Click to expand...

nope. that gives me the same error
 
Hrm. Since you will want to calculate the sums over BOTH queries in theUNION,
you'll probably have to do the UNION  just of the raw data, and do the sums in
a separate query based on the UNION query. Try
Click to expand...
SELECT PlayerName, Team, Sum(IIf([Result]="w",1,0)) AS Won,
Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM
(SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team, Result,
Runout
FROM tblResults2
UNION ALL
SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Result, Runout1
FROM tblResults2);
--
Click to expand...

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com
Click to expand...

nope. that gives me the same error- Hide quoted text -

- Show quoted text -
Click to expand...

I used this to get 1 query and then i can total from there. But i
would like to do it in one query

SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;

I total with this query

SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;

Can i do that in one query?
 
what am i doing wrong?
Hrm. Since you will want to calculate the sums over BOTH queries in the UNION,
you'll probably have to do the UNION  just of the raw data, and do the sums in
a separate query based on the UNION query. Try
SELECT PlayerName, Team, Sum(IIf([Result]="w",1,0)) AS Won,
Sum(IIf([Result]="l",1,0)) AS Lost,
Sum(IIf([Runout]="x",1,0)) AS Runouts
FROM
(SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team, Result,
Runout
FROM tblResults2
UNION ALL
SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away
AS Team, Result, Runout1
FROM tblResults2);
Click to expand...
Click to expand...
nope. that gives me the same error- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I used this to get 1 query and then i can total from there. But i
would like to do it in one query

SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;

I total with this query

SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;

Can i do that in one query?- Hide quoted text -

- Show quoted text -
Click to expand...

Here is my main issue. We are having a website built and i need to use
mySQL so i have to have everything in a table. mySQL doesn't do a
query off of a query.

So i need to get these results into a table. Can a union query also be
a make table query?
 
SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;

I total with this query

SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;

Can i do that in one query?
Click to expand...

You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work. I'd omit
fields you're not using (game date frex) and try:


SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;

Note the square brackets and the closing period around the subquery.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;
Click to expand...
I total with this query
Click to expand...
SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;
Click to expand...
Can i do that in one query?
Click to expand...

You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work. I'd omit
fields you're not using (game date frex) and try:

SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;

Note the square brackets and the closing period around the subquery.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

That one works. Thanks.

How about creating a table from a union query. Is that possible?

Also, i have been on the forms board because I am trying to update my
table with all the data at once. Right now I do it single form one
line at a time. And I can do it continuos form all 21 lines. But
here's the rub. On my single form i have combo boxes for the players
that are populated when i enter the match. In other words, I enter say
Team A verus Team B and each combo box than shows a drop down list of
player. That way i can't misspell anyone's name. Problem is on a
continuous form, all the names change every time i change then name.
Is there a way to stop that?
 
Nope, that is the behavior you see in a continuous form.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;
Click to expand...
I total with this query
SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;
Can i do that in one query?
Click to expand...
You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work. I'd omit
fields you're not using (game date frex) and try:

SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;

Note the square brackets and the closing period around the subquery.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

That one works. Thanks.

How about creating a table from a union query. Is that possible?

Also, i have been on the forms board because I am trying to update my
table with all the data at once. Right now I do it single form one
line at a time. And I can do it continuos form all 21 lines. But
here's the rub. On my single form i have combo boxes for the players
that are populated when i enter the match. In other words, I enter say
Team A verus Team B and each combo box than shows a drop down list of
player. That way i can't misspell anyone's name. Problem is on a
continuous form, all the names change every time i change then name.
Is there a way to stop that?
Click to expand...
 
Nope, that is the behavior you see in a continuous form.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;
I total with this query
SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;
Can i do that in one query?
You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work. I'd omit
fields you're not using (game date frex) and try:
SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;
Note the square brackets and the closing period around the subquery.
--
             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com-Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
That one works. Thanks.
Click to expand...
How about creating a table from a union query. Is that possible?
Click to expand...
Also, i have been on the forms board because I am trying to update my
table with all the data at once. Right now I do it single form one
line at a time. And I can do it continuos form all 21 lines. But
here's the rub. On my single form i have combo boxes for the players
that are populated when i enter the match. In other words, I enter say
Team A verus Team B and each combo box than shows a drop down list of
player. That way i can't misspell anyone's name. Problem is on a
continuous form, all the names change every time i change then name.
Is there a way to stop that?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks. Is there a way to create a table from a Union Query?
 
Note the square brackets and the closing period around the
subquery.
Click to expand...

You also need a name for it, don't you?

FROM tblResults2]. As Something

....I've never created one that didn't.

In a passthrough, of course, you'd use standard () instead of
Jet/ACE's idiosyncratic square brackets with trailing period. And if
you're in SQL 92 mode, you'd also use that, but I don't know how
Access in SQL 92 mode interacts with db's like MySQL.
 
Yes.

Save the union query and then use it as the source for a make table query.

Very unusual to need to do this. Probably not a good idea, but I don't know
your data or why you would want to do this.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Nope, that is the behavior you see in a continuous form.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


On Jul 31, 10:42 am, John W. Vinson
SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;
I total with this query
SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;
Can i do that in one query?
You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work. I'd omit
fields you're not using (game date frex) and try:
SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;
Note the square brackets and the closing period around the subquery.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com-Hide quoted text -
- Show quoted text -
That one works. Thanks.
How about creating a table from a union query. Is that possible?
Also, i have been on the forms board because I am trying to update my
table with all the data at once. Right now I do it single form one
line at a time. And I can do it continuos form all 21 lines. But
here's the rub. On my single form i have combo boxes for the players
that are populated when i enter the match. In other words, I enter say
Team A verus Team B and each combo box than shows a drop down list of
player. That way i can't misspell anyone's name. Problem is on a
continuous form, all the names change every time i change then name.
Is there a way to stop that?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Thanks. Is there a way to create a table from a Union Query?
Click to expand...
 
Actually if you don't give it a name Access will give it a garbage name -
random collection of characters.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Note the square brackets and the closing period around the
subquery.
Click to expand...

You also need a name for it, don't you?

FROM tblResults2]. As Something

...I've never created one that didn't.

In a passthrough, of course, you'd use standard () instead of
Jet/ACE's idiosyncratic square brackets with trailing period. And if
you're in SQL 92 mode, you'd also use that, but I don't know how
Access in SQL 92 mode interacts with db's like MySQL.
Click to expand...
 
Yes.

Save the union query and then use it as the source for a make table query..

Very unusual to need to do this.  Probably not a good idea, but I don'tknow
your data or why you would want to do this.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


Nope, that is the behavior you see in a continuous form.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
pat67 wrote:
On Jul 31, 10:42 am, John W. Vinson
SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;
I total with this query
SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;
Can i do that in one query?
You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work. I'd omit
fields you're not using (game date frex) and try:
SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.AwayAS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;
Note the square brackets and the closing period around the subquery.
--
             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com-Hidequoted text -
- Show quoted text -
That one works. Thanks.
How about creating a table from a union query. Is that possible?
Also, i have been on the forms board because I am trying to update my
table with all the data at once. Right now I do it single form one
line at a time. And I can do it continuos form all 21 lines. But
here's the rub. On my single form i have combo boxes for the players
that are populated when i enter the match. In other words, I enter say
Team A verus Team B and each combo box than shows a drop down list of
player. That way i can't misspell anyone's name. Problem is on a
continuous form, all the names change every time i change then name.
Is there a way to stop that?- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Thanks. Is there a way to create a table from a Union Query?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The reason i make a table is twofold. first is as the season
progresses, there are queries that take longer to run. So i make them
make-table queries and then run my reports from the table. that way
only when i make the table does it take a long time. otherwise i takes
a long time for every report.
The second reason is that when i transfer to mySQL eventually, it will
be easier sice mySQL only runs queries off of tables. so i need to
create table and the run output queries after. but taht will be a
while since the sql is different.

Anyway thanks for your help but i need another answer if you don't
mind

Currently i have a query like this

SELECT myData.PlayerID, Count(qlatestLost.PlayerID) AS
currentWinStreak
FROM myData LEFT JOIN qlatestLost ON myData.playerID =
qlatestLost.playerID
GROUP BY myData.PlayerID;


What i want to do is replace myData and qlatestLost with their actual
queries. basically the from statement myData is replaced by

SELECT myData.PlayerID, Max(IIf(isWin,-1,1)*GameID) AS Mgame
FROM (SELECT GameID, Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))) AS myData
GROUP BY myData.PlayerID;

and then the join qlatest lost is replaced by

SELECT GameID, Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)));


So this is what i have but it is not working.

SELECT myData.PlayerID, Count(qlatestLost.PlayerID) AS
currentWinStreak
FROM (SELECT myData.PlayerID, Max(IIf(isWin,-1,1)*GameID) AS Mgame
FROM (SELECT GameID, Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))) AS myData) LEFT JOIN (SELECT GameID,
Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))) AS qlatestLost ON myData.playerID =
qlatestLost.playerID
GROUP BY myData.PlayerID;

I get syntax error in join operation. can you help?
 
Yes.
Click to expand...
Save the union query and then use it as the source for a make table query.
Click to expand...
Very unusual to need to do this.  Probably not a good idea, but I don't know
your data or why you would want to do this.
Click to expand...
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
pat67 said:
Nope, that is the behavior you see in a continuous form.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
pat67 wrote:
On Jul 31, 10:42 am, John W. Vinson
SELECT tblResults2.ID AS GameID, tblResults2.Date AS GameDate,
tblResults2.Player AS PlayerName, tblResults2.Home AS Team,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.ID AS GameID, tblResults2.Date AS
GameDate, tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2;
I total with this query
SELECT qryUnion3.PlayerName, qryUnion3.Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM qryUnion3
GROUP BY qryUnion3.PlayerName, qryUnion3.Team;
Can i do that in one query?
You may need to consult MySQL documentation (or peer support, or whatever) to
find MySQL's syntax for Subqueries, but yes a subquery should work.. I'd omit
fields you're not using (game date frex) and try:
SELECT PlayerName, Team,
Sum(IIf([PlayerResult]="w",1,0)) AS Won, Sum(IIf([PlayerResult]="l",
1,0)) AS Lost, Sum(IIf([RO]="x",1,0)) AS Runouts
FROM [SELECT tblResults2.Player AS PlayerName, tblResults2.Home ASTeam,
tblResults2.Result AS PlayerResult, tblResults2.Runout AS RO
FROM tblResults2
UNION ALL SELECT tblResults2.Player1 AS PlayerName, tblResults2.Away AS Team,
tblResults2.Result1 AS PlayerResult, tblResults2.Runout1 AS RO
FROM tblResults2].
GROUP BY PlayerName, Team;
Note the square brackets and the closing period around the subquery.
--
             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/http://social...
and see alsohttp://www.utteraccess.com-Hidequotedtext -
- Show quoted text -
That one works. Thanks.
How about creating a table from a union query. Is that possible?
Also, i have been on the forms board because I am trying to update my
table with all the data at once. Right now I do it single form one
line at a time. And I can do it continuos form all 21 lines. But
here's the rub. On my single form i have combo boxes for the players
that are populated when i enter the match. In other words, I enter say
Team A verus Team B and each combo box than shows a drop down list of
player. That way i can't misspell anyone's name. Problem is on a
continuous form, all the names change every time i change then name..
Is there a way to stop that?- Hide quoted text -
- Show quoted text -
Thanks. Is there a way to create a table from a Union Query?- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

The reason i make a table is twofold. first is as the season
progresses, there are queries that take longer to run. So i make them
make-table queries and then run my reports from the table. that way
only when i make the table does it take a long time. otherwise i takes
a long time for every report.
The second reason is that when i transfer to mySQL eventually, it will
be easier sice mySQL only runs queries off of tables. so i need to
create table and the run output queries after. but taht will be a
while since the sql is different.

Anyway thanks for your help but i need another answer if you don't
mind

Currently i have a query like this

SELECT myData.PlayerID, Count(qlatestLost.PlayerID) AS
currentWinStreak
FROM myData LEFT JOIN qlatestLost ON myData.playerID =
qlatestLost.playerID
GROUP BY myData.PlayerID;

What i want to do is replace myData and qlatestLost with their actual
queries. basically the from statement myData is replaced by

SELECT myData.PlayerID, Max(IIf(isWin,-1,1)*GameID) AS Mgame
FROM (SELECT GameID, Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))) AS myData
GROUP BY myData.PlayerID;

and then the join qlatest lost is replaced by

SELECT GameID, Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)));

So this is what i have but it is not working.

SELECT myData.PlayerID, Count(qlatestLost.PlayerID) AS
currentWinStreak
FROM (SELECT myData.PlayerID, Max(IIf(isWin,-1,1)*GameID) AS Mgame
FROM (SELECT GameID, Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))) AS myData) LEFT JOIN (SELECT GameID,
Winner As PlayerID, true AS isWin
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))
UNION ALL SELECT GameID, Loser, false
FROM (SELECT tblStats_All.GameID, tblStats_All.GameDate,
tblStats_All.HomeTeam, tblStats_All.PlayerName AS Winner,
tblStats_All.Team, tblStats_All.Opponent AS Loser,
tblStats_All.Op_Team, tblStats_All.Won
FROM tblStats_All
WHERE (((tblStats_All.Won)=1)))) AS qlatestLost ON myData.playerID =
qlatestLost.playerID
GROUP BY myData.PlayerID;

I get syntax error in join operation. can you help?- Hide quoted text -

- Show quoted text -
Click to expand...

I fixed it. Thanks
 
Back
Top