Union query not showing all fields

[NeoFax]

I have a query that I would like to see the column PlayDate, but it
only shows Winner and Loser. Here is the SQL:

SELECT tblScores2.fldDate AS PlayDate, tblScores2.fldPlayerName AS
Winner
FROM ([Teams in League] INNER JOIN Rosters ON [Teams in League].ID =
Rosters.TeamID) INNER JOIN tblScores2 ON Rosters.[Player Name] =
tblScores2.fldPlayerName
WHERE (((tblScores2.fldWinLose)=-1))
GROUP BY tblScores2.fldDate, tblScores2.fldPlayerName;

UNION SELECT tblScores2.fldDate AS PlayDate, tblScores2.fldPlayerName
AS Loser
FROM ([Teams in League] INNER JOIN Rosters ON [Teams in League].ID =
Rosters.TeamID) INNER JOIN tblScores2 ON Rosters.[Player Name] =
tblScores2.fldPlayerName
WHERE (((tblScores2.fldWinLose)=0))
GROUP BY tblScores2.fldDate, tblScores2.fldPlayerName;

 

[Douglas J. Steele]

That can't be your SQL, as it's invalid. The semicolon in the middle would
prevent the query from running.

 

[NeoFax]

Even without the semi-colon, I don't think this is going to return what you
want.  And I'm not really sure why you have the [Teams in League] and
[Rosters] tables in the query at all, since you are not using those in any
WHERE clause/

I think what you want is something like:

PlayDate    Winner     Loser
5/20/09       Bob         Tom

Is that correct?  If so, I think you could get that directly from tblScores,
using a Crosstab query that looks something like:

TRANSFORM First(tblScores2.fldPlayerName) AS FirstOffldPlayerName
SELECT tblScores2.fldDate
FROM tblScores2
GROUP BY tblScores2.fldDate
PIVOT IIf([fldWinLose]=-1,"Winner","Loser");

The only problem with this is that you would also need some way to reference
a match or some other way to differentiate between all of the events occuring
on a given day.  That would modify the query to something like:

TRANSFORM First(tblScores2.fldPlayerName) AS FirstOffldPlayerName
SELECT tblScores2.fldDate, tblScores2.fldMatch
FROM tblScores2
GROUP BY tblScores2.fldDate
PIVOT IIf([fldWinLose]=-1,"Winner","Loser");

HTH
Dale

I have a query that I would like to see the column PlayDate, but it
only shows Winner and Loser.  Here is the SQL:

SELECT tblScores2.fldDate AS PlayDate, tblScores2.fldPlayerName AS
Winner
FROM ([Teams in League] INNER JOIN Rosters ON [Teams in League].ID =
Rosters.TeamID) INNER JOIN tblScores2 ON Rosters.[Player Name] =
tblScores2.fldPlayerName
WHERE (((tblScores2.fldWinLose)=-1))
GROUP BY tblScores2.fldDate, tblScores2.fldPlayerName;

UNION SELECT tblScores2.fldDate AS PlayDate, tblScores2.fldPlayerName
AS Loser
FROM ([Teams in League] INNER JOIN Rosters ON [Teams in League].ID =
Rosters.TeamID) INNER JOIN tblScores2 ON Rosters.[Player Name] =
tblScores2.fldPlayerName
WHERE (((tblScores2.fldWinLose)=0))
GROUP BY tblScores2.fldDate, tblScores2.fldPlayerName;

--
HTH

Dale Fye

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

And what data would be in the fldMatch field? The number of the match
that day? Name of the match?

 

[NeoFax]

Even without the semi-colon, I don't think this is going to return whatyou
want.  And I'm not really sure why you have the [Teams in League] and
[Rosters] tables in the query at all, since you are not using those in any
WHERE clause/

I think what you want is something like:

PlayDate    Winner     Loser
5/20/09       Bob         Tom

Is that correct?  If so, I think you could get that directly from tblScores,
using a Crosstab query that looks something like:

TRANSFORM First(tblScores2.fldPlayerName) AS FirstOffldPlayerName
SELECT tblScores2.fldDate
FROM tblScores2
GROUP BY tblScores2.fldDate
PIVOT IIf([fldWinLose]=-1,"Winner","Loser");

The only problem with this is that you would also need some way to reference
a match or some other way to differentiate between all of the events occuring
on a given day.  That would modify the query to something like:

TRANSFORM First(tblScores2.fldPlayerName) AS FirstOffldPlayerName
SELECT tblScores2.fldDate, tblScores2.fldMatch
FROM tblScores2
GROUP BY tblScores2.fldDate
PIVOT IIf([fldWinLose]=-1,"Winner","Loser");

HTH
Dale

I have a query that I would like to see the column PlayDate, but it
only shows Winner and Loser.  Here is the SQL:
SELECT tblScores2.fldDate AS PlayDate, tblScores2.fldPlayerName AS
Winner
FROM ([Teams in League] INNER JOIN Rosters ON [Teams in League].ID =
Rosters.TeamID) INNER JOIN tblScores2 ON Rosters.[Player Name] =
tblScores2.fldPlayerName
WHERE (((tblScores2.fldWinLose)=-1))
GROUP BY tblScores2.fldDate, tblScores2.fldPlayerName;
UNION SELECT tblScores2.fldDate AS PlayDate, tblScores2.fldPlayerName
AS Loser
FROM ([Teams in League] INNER JOIN Rosters ON [Teams in League].ID =
Rosters.TeamID) INNER JOIN tblScores2 ON Rosters.[Player Name] =
tblScores2.fldPlayerName
WHERE (((tblScores2.fldWinLose)=0))
GROUP BY tblScores2.fldDate, tblScores2.fldPlayerName;

Dale Fye

Message posted viahttp://www.accessmonster.com-Hide quoted text -

- Show quoted text -

And what data would be in the fldMatch field?  The number of the match
that day?  Name of the match?- Hide quoted text -

- Show quoted text -

I set fldMatch to Number:Integer and use this as the number of the
match each day between the teams. There is 4 matches each time
between two teams.

 

[NeoFax]

Then maybe that will work.  What other fields are in tblScores2?  Give me the
field name and what it represents.

Did you try the crosstab query with this fldMatch added?  If so, did you get
what you were looking for?

Even without the semi-colon, I don't think this is going to return what you
want.  And I'm not really sure why you have the [Teams in League] and

[quoted text clipped - 60 lines]

- Show quoted text -

I set fldMatch to Number:Integer and use this as the number of the
match each day between the teams.  There is 4 matches each time
between two teams.

--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200905/1

Those are the only fields currently and it gives me exactly what I
want, but it is for a friend and I may need to change it to display
some other nonsense he comes up with (No database experience).

BTW here is the SQL of the original question:
SELECT tblScores.Date, [Teams in League].[Team Name],
tblScores.Winner, tblScores.Loser
FROM [Teams in League] INNER JOIN (tblScores INNER JOIN Rosters ON
tblScores.Winner = Rosters.[Player Name]) ON [Teams in League].ID =
Rosters.TeamID
GROUP BY tblScores.Date, [Teams in League].[Team Name],
tblScores.Winner, tblScores.Loser;

I guess Access automatically changed what I wrote into this to make it
work. So now I have two queries that do exactly the same thing.