Use of Distinct?

[wgoldfarb]

I have a Query created with the Access Query designer. What I am trying to do
is the following: I have a table of 'leads', consisting of information
including a property address and an Owner ID. I have a separate 'owners'
table with detailed information for each owner (name, address, etc). Both
tables are linked through an owner ID numeric index field. Each owner could
have more than one property, but each property has only one owner.

As a slight complication, all addresses (property addresses in the 'leads'
table and owner addresses in the 'owners' table) include a city, and there is
a separate, linked table with the names of relevant cities. So, the 'leads'
table and 'owners' table don't include the city name or details, but instead
simply have a City ID field which links into the 'cities' table.

I created a query (using the query designer in Access) that selects all
leads of a certain status. The SQL statement created by access is the
following:

SELECT leads.leadID, leads.status, leads.occupancy, leads.homeaddress1,
cities_1.cityname, leads.homestate, owners.name, owners.salutation,
owners.address1, owners.careof, cities.cityname, owners.state,
owners.postalcode
FROM (cities RIGHT JOIN owners ON cities.cityID = owners.City) INNER JOIN
(leads LEFT JOIN cities AS cities_1 ON leads.Homecity = cities_1.cityID) ON
owners.ownerID = leads.owner
WHERE (((leads.status)=1) AND ((leads.occupancy) Between 2 And 3))
ORDER BY leads.leadID;


This seems a bit complicated, and could probably be made simpler, but not
being very familiar with SQL statements I decided not to fool around with it.

What I would like to do next is eliminate any duplicate owners. In other
words, if there is an owner with more than one property, I would only like
the query to include only one property for that owner (it does not matter
which property).

I have read several ways of doing it (including a "FIRST" or "LAST"
statement, or first doing a SELECT DISTINCT on the owners table and using the
results of that instead of the owners table) but all the examples I have seen
are very simple, so I really don't know how to modify this SQL statement to
use any of those possibilities.

Any help will be greatly appreciated.

Thanks!

 

[David S via AccessMonster.com]

What I would like to do next is eliminate any duplicate owners. In other

words, if there is an owner with more than one property, I would only like
the query to include only one property for that owner (it does not matter
which property).

You could use your existing query as the foundation to eliminate duplicate
owners, but that seems a bit risky to me - I'd prefer to start over with a
number of separate queries, which should help you to understand the process
and make debugging easier.

Since you want to have each owner appear once, this would be the logical
place to start - to get the owners and only one lead, you could the following
OwnersWithLeads query:
SELECT leads.owner, Max(leads.leadID) AS MaxOfleadID
FROM leads
WHERE (((leads.status)=1) AND ((leads.occupancy) Between 2 And 3))
GROUP BY leads.owner;

Given that you don't care which property appears, you could also use Min
(leads.leadsID)

We can then use this to get the lead and city details - we can use your other
query, but instead of joining Owners with Leads directly, we go through this
new table. So OwnersWithLeadsDetail looks like:
SELECT leads.leadID, leads.status, leads.occupancy, leads.homeaddress1,
cities_1.cityname, leads.homestate, owners.name, owners.salutation, owners.
address1, owners.careof, cities.cityname, owners.state, owners.postalcode
FROM (OwnersWithLeads INNER JOIN (cities RIGHT JOIN owners ON cities.cityID =
owners.City) ON OwnersWithLeads.owner = owners.ownerID) INNER JOIN (leads
LEFT JOIN cities AS cities_1 ON leads.homecity = cities_1.cityID) ON
OwnersWithLeads.MaxOfleadID = leads.leadID
WHERE (((leads.status)=1) AND ((leads.occupancy) Between 2 And 3))
ORDER BY leads.leadID;

 

[David S via AccessMonster.com]

Oop - just seen from another thread how you could use the First function to
achieve a similar effect:
SELECT First(leads.leadID) AS FirstOfleadID, First(leads.status) AS
FirstOfstatus, First(leads.occupancy) AS FirstOfoccupancy, First(leads.
homeaddress1) AS FirstOfhomeaddress1, First(cities_1.cityname) AS
FirstOfcityname, First(leads.homestate) AS FirstOfhomestate, owners.name,
owners.salutation, owners.address1, owners.careof, cities.cityname, owners.
state, owners.postalcode
FROM (cities RIGHT JOIN owners ON cities.cityID = owners.City) INNER JOIN
(leads LEFT JOIN cities AS cities_1 ON leads.homecity = cities_1.cityID) ON
owners.ownerID = leads.owner
WHERE (((leads.status)=1) AND ((leads.occupancy) Between 2 And 3))
GROUP BY owners.name, owners.salutation, owners.address1, owners.careof,
cities.cityname, owners.state, owners.postalcode;

Personally, I'm not as familiar with the First function and would find it
more confusing than the other approach I use earlier...

 

[wgoldfarb]

David,

Your suggestion worked perfectly! It is exactly what I was looking for.

Regards,

William