Hide dupes in a query

  • Thread starter Thread starter ZenMasta
  • Start date Start date
Z

ZenMasta

I have a query that lists all customers that have ordered from us since a
certain date.

SELECT tblContactData.ChurchName, tblAddresses.DCreated,
tblAddresses.Address1, tblAddresses.City, tblAddresses.State,
tblAddresses.Zip, tblOrderHdr.ShipAddressID, Max(tblOrderHdr.OrderDate) AS
MaxOfOrderDate, tblContactData.ContactID
FROM ((tblContactData INNER JOIN tblAddresses ON tblContactData.ContactID =
tblAddresses.aContactId) INNER JOIN tblStatus ON tblContactData.ConStatusID
= tblStatus.StatusID) INNER JOIN tblOrderHdr ON tblContactData.ContactID =
tblOrderHdr.oContactID
GROUP BY tblContactData.ChurchName, tblAddresses.DCreated,
tblAddresses.PrimaryAddress, tblAddresses.Address1, tblAddresses.City,
tblAddresses.State, tblAddresses.Zip, tblContactData.ConAddressAlert,
tblStatus.StatusID, tblOrderHdr.ShipAddressID, tblContactData.ContactID
HAVING (((tblContactData.ChurchName) Is Not Null) AND
((tblAddresses.DCreated)>#12/1/1990#) AND ((tblAddresses.PrimaryAddress)=-1)
AND ((tblAddresses.Address1) Is Not Null) AND ((tblAddresses.State) Is Not
Null) AND ((tblContactData.ConAddressAlert)=0) AND ((tblStatus.StatusID)=1)
AND ((Max(tblOrderHdr.OrderDate))>#12/31/2008#))
ORDER BY tblContactData.ChurchName;

This is great but it looks like I get duplicate customers appearing in the
list. The field I'd use to check for dupes is tblContactData.ContactID

How can I alter this query so it only shows the customer once?
 
Your GROUP BY cause has 11 fields:
- tblContactData.ChurchName,
- tblAddresses.DCreated,
- tblAddresses.PrimaryAddress,
- tblAddresses.Address1,
- tblAddresses.City,
- tblAddresses.State,
- tblAddresses.Zip,
- tblContactData.ConAddressAlert,
- tblStatus.StatusID,
- tblOrderHdr.ShipAddressID,
- tblContactData.ContactID

Consequently, the query returns a row for every combination of those 11
fields.

If you want to group by ChurchName only, change the Totals row in query
design so it uses (say) First instead of Group By. The query now returns
only one row per ChurchName, and whatever is the first thing match it finds
for the other fields.
 
Thanks,
I didn't design this query originally so after looking at it, it was a bit
confusing. I ended up figuring out how to create the query I wanted.
 
ZenMasta said:
Thanks,
I didn't design this query originally so after looking at it, it was a bit
confusing. I ended up figuring out how to create the query I wanted.
Click to expand...
 
Back
Top