Query Revision - Help Required

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul W Smith - 21 Apr 2008 21:30 GMT

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

Reply to this Message

Al Campagna - 21 Apr 2008 22:43 GMT

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.

Signature

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
[quoted text clipped - 10 lines]
>
> Paul Smith

Reply to this Message

Paul W Smith - 21 Apr 2008 22:01 GMT

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.
[quoted text clipped - 16 lines]
>>
>> Paul Smith

Reply to this Message

John Spencer - 21 Apr 2008 23:35 GMT

I believe I would write it this way.

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

which is slightly different from
SELECT tPlayers.DateOfBirth, tPlayers.FullName, tPlayers.ClubID,
tPlayers.DateRegistered, tPlayers.Active
FROM tPlayers
WHERE tfPlayers.Active = TRUE AND
tPlayers.DateOfBirth In
(SELECT [DateOfBirth]
FROM [tPlayers] As Tmp
GROUP BY [DateOfBirth],[FullName]
HAVING Count(*)>1
And Tmp.[FullName] = [tPlayers].[FullName])
ORDER BY tPlayers.DateOfBirth, tPlayers.FullName;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> To be honest that is what I have been trying to do, that is how I usually
> have to do my sub queries.
[quoted text clipped - 26 lines]
>>>
>>> Paul Smith

Reply to this Message

Paul W Smith - 21 Apr 2008 23:04 GMT

John,

Thank you for your reply.

I have no clue what either of your queries below arew showing, the second
returns nothing, while the first returns a set of records i cannot see what
they have in common - all have different DOBs so I am totally confused.

Neither of your queries returns the subset of records I require, and this is
obviously because I did not take the time to explain what I am trying to do.
Which is....

I have a table (tPlayers) of records for individuals, individuals who move
around between clubs ClubID). When an individual moves to a new club his
old record is marked inactive (Active is set to false), and a new one is
created. I am not always notified by the new club that the player was
previously registerd with an old club.

Unfortuantely as in life people use different names or combination of names,
so it can happen that the same player could be marked as an active member of
two clubs. I am trying to write a query that will highlight any such cases
ot at leats highlight any potential issues.

What I am looking for is a query that returns a listing (DOB, FullName,
ClubID, DateRegistered & Active, for all records who have the same DOB AND
both have Active = True. It is adding this last condition that is giving me
problems.

This is driving me mad!!!

Paul Smith

>I believe I would write it this way.
>
[quoted text clipped - 61 lines]
>>>>
>>>> Paul Smith

Reply to this Message

John Spencer - 22 Apr 2008 01:55 GMT

It sounds as if you need this query. Which should be giving you all
records that are possible duplicates. The records should be in sets of
two or more where the dateofBirth and FullName of adjacent records is
the same.

If that isn't the case then I have obviously done something wrong in the
query.

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

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> John,
>
[quoted text clipped - 93 lines]
>>>>>
>>>>> Paul Smith

Reply to this Message

Dale Fye - 22 Apr 2008 15:28 GMT

Paul,

From your description, it sounds like the various clubs are entering these
individuals into tPlayers. Is that correct?

I think part of your problem, as you have indicated, is that individuals
don't necessarily use the same name (eg. Dave, David). So using a [FullName]
field may result in a problem, when this occurs, as Count(*) will not be > 1
if grouped by DOB and FullName (could also be as simple as someone typing two
spaces between names).

Do you have a LastName field, or address, or phone # field in your database?
You may have to do several queries with several of these combinations to
identify everyone that is in the table with more than one active record.

Dale

Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

> John,
>
[quoted text clipped - 93 lines]
> >>>>
> >>>> Paul Smith

Reply to this Message

Steve Sanford - 22 Apr 2008 10:41 GMT

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??

Signature

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.
[quoted text clipped - 26 lines]
> >>
> >> Paul Smith

Reply to this Message

Paul W Smith - 22 Apr 2008 13:45 GMT

Thank you both, your assistance has been appreciated.

Paul Smith

> The criteria [Active]= TRUE not should be in the subquery.
>
[quoted text clipped - 52 lines]
>> >>
>> >> Paul Smith

Reply to this Message