Query Revision - Help Required

[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

 

[Paul W 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

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

 

[Al Campagna]

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."

 

[Steve Sanford]

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.)

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

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

 

[Paul W Smith]

Thank you both, your assistance has been appreciated.

Paul Smith

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.)

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

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