Trouble with or statement and allowing duplicates

[Matt P]

This is the SQL of my query:
SELECT DISTINCT tblContacts.fldCity, tblHistory.fldType
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
GROUP BY tblContacts.fldCity, tblHistory.fldType
HAVING (((tblHistory.fldType)="HOTEL" Or (tblHistory.fldType)
="MOTEL"));

The problem is that it's allowing duplicates with the Or statement
included... so it is acting almost as if And / Or at the same time and
not just Or. What are some suggestions to remove the duplicates?
Thanks

 

[Ken Snell MVP]

The records are "duplicated" because you're including the fldType field in
the GROUP BY clause. Try this:

SELECT tblContacts.fldCity
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE (((tblHistory.fldType)="HOTEL" Or (tblHistory.fldType)
="MOTEL"))
GROUP BY tblContacts.fldCity;

 

[Bob Barrows]

This is the SQL of my query:
SELECT DISTINCT tblContacts.fldCity, tblHistory.fldType
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
GROUP BY tblContacts.fldCity, tblHistory.fldType
HAVING (((tblHistory.fldType)="HOTEL" Or (tblHistory.fldType)
="MOTEL"));

The problem is that it's allowing duplicates with the Or statement
included... so it is acting almost as if And / Or at the same time and
not just Or. What are some suggestions to remove the duplicates?
Thanks

First of all, nothing to do with your problem, but you have to get that
criteria out of the HAVING clause and put it into a WHERE clause. There
is nothing in that criteria that requires aggregated data so it should
be put in the WHERE clause so it will be evaluated BEFORE the grouping
occurs. The more you can limit the number of records that have to be
grouped and aggregated, the better performance will be. The WHERE clause
needs to be put before the GROUP BY clause

Second of all, GROUP BY forces the creation of unique records, so that
DISTINCT keyword is completely unnecessary.

SELECT tblContacts.fldCity, tblHistory.fldType
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE (((tblHistory.fldType)="HOTEL" Or (tblHistory.fldType)
="MOTEL"))
GROUP BY tblContacts.fldCity, tblHistory.fldType;

or
SELECT tblContacts.fldCity, tblHistory.fldType
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE tblHistory.fldType IN ("HOTEL","MOTEL")
GROUP BY tblContacts.fldCity, tblHistory.fldType;

Are you planning to add any aggregated fields (COUNT, SUM, etc)? If not,
the GROUP BY is not even needed - use DISTINCT instead:

SELECT DISTINCT tblContacts.fldCity, tblHistory.fldType
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE tblHistory.fldType IN ("HOTEL","MOTEL")

Thirdly, and now we get to your question, I don't believe there are
duplicate records. Duplicates are impossible with GROUP BY. Please show
an example of a couple records you consider to be duplicates (making
sure to show the original records that resulted in these duplicates).

 

[Matt P]

The records are "duplicated" because you're including the fldType field in
the GROUP BY clause. Try this:

SELECT tblContacts.fldCity
FROM tblContacts INNER JOIN tblHistory ON tblContacts.fldContactsID =
tblHistory.fldContactID
WHERE (((tblHistory.fldType)="HOTEL" Or (tblHistory.fldType)
="MOTEL"))
GROUP BY tblContacts.fldCity;

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/







- Show quoted text -

Thanks, worked perfect... I was thinking about that with the "Group
By" but I never would have guessed the "Where" so thanks a bunch!