Query with multiple criteria returns duplicates

  • Thread starter Thread starter meghanwh
  • Start date Start date
M

meghanwh

I have a query that has 6 criteria (No mail AND group=10, no mail AND
young society=true, etc.). If someone meets more than 1 criteria, they
are listed twice. How do I eliminate this? I've just been creating the
query in design view, so I have the AND statements on the same line
and the OR statements on separate lines. Here's the SQL:

SELECT DISTINCT [New People Query].UID, [New People Query].[No Mail],
[New People Query].PRFX, [New People Query].[F-NAME], [New People
Query].[M-NAME], [New People Query].[L-NAME], PplACS.[Alumni Code],
[New People Query].[Young Society], [New People Query].Ranking, [New
People Query].Pref1, [New People Query].Pref2, [New People
Query].PrefStrt, [New People Query].PrefCity, [New People
Query].PrefState, [New People Query].PrefZip
FROM PplACS INNER JOIN [New People Query] ON PplACS.UID = [New People
Query].UID
WHERE ((([New People Query].[No Mail])=False) AND ((PplACS.[Alumni
Code])="10")) OR ((([New People Query].[No Mail])=False) AND (([New
People Query].[Young Society])=True)) OR ((([New People Query].[No
Mail])=False) AND (([New People Query].Ranking)="1" Or ([New People
Query].Ranking)="2" Or ([New People Query].Ranking)="3" Or ([New
People Query].Ranking)="$50M+" Or ([New People Query].Ranking)="$100M
+"));

Thanks.
 
I have a query that has 6 criteria (No mail AND group=10, no mail AND
young society=true, etc.). If someone meets more than 1 criteria, they
are listed twice. How do I eliminate this? I've just been creating the
query in design view, so I have the AND statements on the same line
and the OR statements on separate lines. Here's the SQL:

SELECT DISTINCT [New People Query].UID, [New People Query].[No Mail],
[New People Query].PRFX, [New People Query].[F-NAME], [New People
Query].[M-NAME], [New People Query].[L-NAME], PplACS.[Alumni Code],
[New People Query].[Young Society], [New People Query].Ranking, [New
People Query].Pref1, [New People Query].Pref2, [New People
Query].PrefStrt, [New People Query].PrefCity, [New People
Query].PrefState, [New People Query].PrefZip
FROM PplACS INNER JOIN [New People Query] ON PplACS.UID = [New People
Query].UID
WHERE ((([New People Query].[No Mail])=False) AND ((PplACS.[Alumni
Code])="10")) OR ((([New People Query].[No Mail])=False) AND (([New
People Query].[Young Society])=True)) OR ((([New People Query].[No
Mail])=False) AND (([New People Query].Ranking)="1" Or ([New People
Query].Ranking)="2" Or ([New People Query].Ranking)="3" Or ([New
People Query].Ranking)="$50M+" Or ([New People Query].Ranking)="$100M
+"));
Click to expand...
Technically, the DISTINCT should ensure no record is listed twice...

and the reason it is not is because one or more of the fields in your
SELECT clause is different for the same UID.

Start a new query and go to SQL View, then paste following

SELECT
Q.UID,
Q.[No Mail],
Q.PRFX,
Q.[F-NAME],
Q.[M-NAME],
Q.[L-NAME],
P.[Alumni Code],
Q.[Young Society],
Q.Ranking,
Q.Pref1,
Q.Pref2,
Q.PrefStrt,
Q.PrefCity,
Q.PrefState,
Q.PrefZip
FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
GROUP BY
Q.UID,
Q.[No Mail],
Q.PRFX,
Q.[F-NAME],
Q.[M-NAME],
Q.[L-NAME],
P.[Alumni Code],
Q.[Young Society],
Q.Ranking,
Q.Pref1,
Q.Pref2,
Q.PrefStrt,
Q.PrefCity,
Q.PrefState,
Q.PrefZip;

This should provide same set of records as your original
query (including "twice"). But, you can go into Design View
and change "Total:" from "Group By" to "First" for the
field (or fields) that are causing the "twice."
 
I have a query that has 6 criteria (No mail AND group=10, no mail AND
young society=true, etc.). If someone meets more than 1 criteria, they
are listed twice. How do I eliminate this? I've just been creating the
query in design view, so I have the AND statements on the same line
and the OR statements on separate lines. Here's the SQL:
Click to expand...
SELECT DISTINCT [New People Query].UID, [New People Query].[No Mail],
[New People Query].PRFX, [New People Query].[F-NAME], [New People
Query].[M-NAME], [New People Query].[L-NAME], PplACS.[Alumni Code],
[New People Query].[Young Society], [New People Query].Ranking, [New
People Query].Pref1, [New People Query].Pref2, [New People
Query].PrefStrt, [New People Query].PrefCity, [New People
Query].PrefState, [New People Query].PrefZip
FROM PplACS INNER JOIN [New People Query] ON PplACS.UID = [New People
Query].UID
WHERE ((([New People Query].[No Mail])=False) AND ((PplACS.[Alumni
Code])="10")) OR ((([New People Query].[No Mail])=False) AND (([New
People Query].[Young Society])=True)) OR ((([New People Query].[No
Mail])=False) AND (([New People Query].Ranking)="1" Or ([New People
Query].Ranking)="2" Or ([New People Query].Ranking)="3" Or ([New
People Query].Ranking)="$50M+" Or ([New People Query].Ranking)="$100M
+"));
Click to expand...

Technically, the DISTINCT should ensure no record is listed twice...

and the reason it is not is because one or more of the fields in your
SELECT clause is different for the same UID.

Start a new query and go to SQL View, then paste following

SELECT
Q.UID,
Q.[No Mail],
Q.PRFX,
Q.[F-NAME],
Q.[M-NAME],
Q.[L-NAME],
P.[Alumni Code],
Q.[Young Society],
Q.Ranking,
Q.Pref1,
Q.Pref2,
Q.PrefStrt,
Q.PrefCity,
Q.PrefState,
Q.PrefZip
FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
GROUP BY
Q.UID,
Q.[No Mail],
Q.PRFX,
Q.[F-NAME],
Q.[M-NAME],
Q.[L-NAME],
P.[Alumni Code],
Q.[Young Society],
Q.Ranking,
Q.Pref1,
Q.Pref2,
Q.PrefStrt,
Q.PrefCity,
Q.PrefState,
Q.PrefZip;

This should provide same set of records as your original
query (including "twice"). But, you can go into Design View
and change "Total:" from "Group By" to "First" for the
field (or fields) that are causing the "twice."- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you, that works great. Now, if I have another table called One K
Last 3 that I want to add, so if people meet any of the criteria above
OR are on the new table, how would I do that? Do I need a new query?
The new table is just one field, UID.
 
If I understand correctly, one way:

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
EXISTS
(SELECT
O.UID
FROM
[One K Last 3] AS O
WHERE
O.UID = P.UID)
)

or, another way:

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
P.UID IN
(SELECT
O.UID
FROM
[One K Last 3] AS O)
)
 
If I understand correctly, one way:

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
EXISTS
(SELECT
O.UID
FROM
[One K Last 3] AS O
WHERE
O.UID = P.UID)
)

or, another way:

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
P.UID IN
(SELECT
O.UID
FROM
[One K Last 3] AS O)
)

<snip>


Thank you, that works great. Now, if I have another table called One K
Last 3 that I want to add, so if people meet any of the criteria above
OR are on the new table, how would I do that? Do I need a new query?
The new table is just one field, UID.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you. When I try both of those I get a message that says "You
tried to execute a query that does not include the specified
expression 'UID' as part of an aggregate function."
 
Sorry...used P.UID in expressions instead of
Q.UID (which is in your groups)...

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
EXISTS
(SELECT
O.UID
FROM
[One K Last 3] AS O
WHERE
O.UID = Q.UID)
)

=================
or, another way:
=================

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
Q.UID IN
(SELECT
O.UID
FROM
[One K Last 3] AS O)
)

I left out SELECT and GROUP BY clauses
because I believe you have changed some from
GROUP BY to FIRST (or some other aggregate)...
 
Sorry...used P.UID in expressions instead of
Q.UID (which is in your groups)...

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
EXISTS
(SELECT
O.UID
FROM
[One K Last 3] AS O
WHERE
O.UID = Q.UID)
)

=================
or, another way:
=================

FROM
PplACS AS P
INNER JOIN
[New People Query] AS Q
ON
P.UID = Q.UID
WHERE
(
(Q.[No Mail]=False)
AND
(
(P.[Alumni Code]="10")
OR
(Q.[Young Society]=True)
OR
(Q.Ranking IN ("1","2","3","$50M+","$100M +"))
)
)
OR
(
Q.UID IN
(SELECT
O.UID
FROM
[One K Last 3] AS O)
)

I left out SELECT and GROUP BY clauses
because I believe you have changed some from
GROUP BY to FIRST (or some other aggregate)...
<snip>


Thank you. When I try both of those I get a message that says "You
tried to execute a query that does not include the specified
expression 'UID' as part of an aggregate function."- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Great thank you. Just wondering, is there a limit to the number of OR
criteria you can have?
 
meghanwh said:
Great thank you. Just wondering, is there a limit to the number of OR
criteria you can have?
Click to expand...

I don't know...

40 AND's according to Help

If you do reach some limit it most likely
will be a result of poorly-designed db...
truly...
 
Back
Top