Query Revision - Help Required

  • Thread starter Thread starter Paul W Smith
  • Start date Start date
P

Paul W Smith

SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE (((tPlayers.DateOfBirth) In (SELECT [DateOfBirth] FROM [tPlayers] As
Tmp GROUP BY [DateOfBirth],[FullName] HAVING Count(*)>1 And [FullName] =
[tPlayers].[FullName] )))
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;

I would like to amend this query so that it now only returns matches where
Active = TRUE

Been trying for two hours and no success SQL skills are not good enough.

Paul Smith
 
To be honest that is what I have been trying to do, that is how I usually
have to do my sub queries.

I have been trying for two hours and cannot get it right.

Can anyone please look at the SQL and re-write if for me - I have tried
myself honestly!

Paul Smith




Al Campagna said:
Paul,
Use the QueryDesign to set up your query.
Just add the Active field to the query grid, and give it a criteria of
True.
When that query works properly, you can go to View/SQL, and "steal" the
correct SQL statement... if you need to.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Paul W Smith said:
SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE (((tPlayers.DateOfBirth) In (SELECT [DateOfBirth] FROM [tPlayers]
As Tmp GROUP BY [DateOfBirth],[FullName] HAVING Count(*)>1 And
[FullName] = [tPlayers].[FullName] )))
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;

I would like to amend this query so that it now only returns matches
where Active = TRUE

Been trying for two hours and no success SQL skills are not good enough.

Paul Smith
Click to expand...
Click to expand...
 
Paul,
Use the QueryDesign to set up your query.
Just add the Active field to the query grid, and give it a criteria of
True.
When that query works properly, you can go to View/SQL, and "steal" the
correct SQL statement... if you need to.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
The criteria [Active]= TRUE not should be in the subquery.

I pasted your SQL into a query as AL suggested and added TRUE in the
criteria for the field "Active".

Here is the SQL:

SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE (((tPlayers.DateOfBirth) In (SELECT Tmp.DateOfBirth FROM tPlayers AS
Tmp GROUP BY Tmp.DateOfBirth, Tmp.FullName HAVING
(((Tmp.FullName)=[tPlayers].[FullName]) AND ((Count(*))>1)))) AND
((tPlayers.Active)=True))
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;


NOTE: If there is only one record for Jim Jones DOB 5/5/1963 and is Active,
this record will *not* be returned by the query.

Is this what you are trying to do??

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Paul W Smith said:
To be honest that is what I have been trying to do, that is how I usually
have to do my sub queries.

I have been trying for two hours and cannot get it right.

Can anyone please look at the SQL and re-write if for me - I have tried
myself honestly!

Paul Smith




Al Campagna said:
Paul,
Use the QueryDesign to set up your query.
Just add the Active field to the query grid, and give it a criteria of
True.
When that query works properly, you can go to View/SQL, and "steal" the
correct SQL statement... if you need to.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Paul W Smith said:
SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE (((tPlayers.DateOfBirth) In (SELECT [DateOfBirth] FROM [tPlayers]
As Tmp GROUP BY [DateOfBirth],[FullName] HAVING Count(*)>1 And
[FullName] = [tPlayers].[FullName] )))
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;

I would like to amend this query so that it now only returns matches
where Active = TRUE

Been trying for two hours and no success SQL skills are not good enough.

Paul Smith
Click to expand...
Click to expand...
Click to expand...
 
Thank you both, your assistance has been appreciated.

Paul Smith


Steve Sanford said:
The criteria [Active]= TRUE not should be in the subquery.

I pasted your SQL into a query as AL suggested and added TRUE in the
criteria for the field "Active".

Here is the SQL:

SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE (((tPlayers.DateOfBirth) In (SELECT Tmp.DateOfBirth FROM tPlayers AS
Tmp GROUP BY Tmp.DateOfBirth, Tmp.FullName HAVING
(((Tmp.FullName)=[tPlayers].[FullName]) AND ((Count(*))>1)))) AND
((tPlayers.Active)=True))
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;


NOTE: If there is only one record for Jim Jones DOB 5/5/1963 and is
Active,
this record will *not* be returned by the query.

Is this what you are trying to do??

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Paul W Smith said:
To be honest that is what I have been trying to do, that is how I usually
have to do my sub queries.

I have been trying for two hours and cannot get it right.

Can anyone please look at the SQL and re-write if for me - I have tried
myself honestly!

Paul Smith




Al Campagna said:
Paul,
Use the QueryDesign to set up your query.
Just add the Active field to the query grid, and give it a criteria
of
True.
When that query works properly, you can go to View/SQL, and "steal"
the
correct SQL statement... if you need to.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE (((tPlayers.DateOfBirth) In (SELECT [DateOfBirth] FROM
[tPlayers]
As Tmp GROUP BY [DateOfBirth],[FullName] HAVING Count(*)>1 And
[FullName] = [tPlayers].[FullName] )))
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;

I would like to amend this query so that it now only returns matches
where Active = TRUE

Been trying for two hours and no success SQL skills are not good
enough.

Paul Smith
Click to expand...
Click to expand...
Click to expand...
 
Back
Top