Using Distinct function along with

[AccessDabbler]

Working on a query. Which would list all the agents first name, last name,
agent tax id, agent city, agent state, zip ,date agent returned order,
active,background check. I have used the distinct function in the design view
of the query but it doesnt function properly. Here is the SQL view of the
query without the distinct. Its giving repetitive values. Would ideally like
just one record of the agent showing up, but isnt happening right now.
SELECT tb_Agent.Agent_LName, tb_Agent.Agent_FName,
tb_Order.Agent_Returned_Order, tb_Agent.Agent_ID, tb_Agent.Agent_City,
tb_Agent.Agent_St, tb_Agent.Agent_Zip, tb_Agent.Agent_Tax_ID,
tb_Agent.Background_Check, tb_Agent.Active
FROM (tb_Agent INNER JOIN tb_Order ON tb_Agent.Agent_ID = tb_Order.Agent_ID)
INNER JOIN tb_Order_Data ON tb_Order.Order_ID = tb_Order_Data.Order_ID
WHERE (((tb_Order.Agent_Returned_Order)>#10/1/2005#) AND
((tb_Agent.Background_Check)=No) AND ((tb_Agent.Active)=Yes));

 

[Tom Ellison]

Dear Dab:

What is it you expect the DISTINCT to do? I can tell you it does work
properly, but that doesn't necessarily mean what you expect it to mean.

Your query returns 10 columns. Using DISTINCT will return only one copy of
any row in which all 10 columns are identical. If one has the City as Saint
Louis and another has St. Louis, then these are distinct and both will be
returned. If one has the Zip 5 and another the Zip 9, they are distinct.
Any single character that is different will make two rows distinct.

On what basis do you wish to define "distinct" for your own purposes?
Perhaps it would be just Agent_ID. So then, as an example, assuming this is
the case, if you have 2 rows for that agent, which one do you want to see.
If one is "Bill" and another "William" which should be shown? What
mechanism will be used to specify which you get? Or do you care?

Tom Ellison

 

[Jerry Whittle]

DISTINCT is functioning properly; it's just not doing what you want.

Chances are that there's multiple Agent_Returned_Order's. That would cause
the agent to show up more than once. If so, which one do you want to return?
Try this for testing. Notice that the WHERE clause is missing. To put it back
in you need to change it to a HAVING clause.

SELECT tb_Agent.Agent_LName,
tb_Agent.Agent_FName,
Max(tb_Order.Agent_Returned_Order),
tb_Agent.Agent_ID,
tb_Agent.Agent_City,
tb_Agent.Agent_St,
tb_Agent.Agent_Zip,
tb_Agent.Agent_Tax_ID,
tb_Agent.Background_Check,
tb_Agent.Active
FROM (tb_Agent INNER JOIN tb_Order ON tb_Agent.Agent_ID = tb_Order.Agent_ID)
INNER JOIN tb_Order_Data ON tb_Order.Order_ID = tb_Order_Data.Order_ID
GROUP BY tb_Agent.Agent_LName,
tb_Agent.Agent_FName,
tb_Agent.Agent_ID,
tb_Agent.Agent_City,
tb_Agent.Agent_St,
tb_Agent.Agent_Zip,
tb_Agent.Agent_Tax_ID,
tb_Agent.Background_Check,
tb_Agent.Active ;


Or you have a many-to-many relationship between the two tables and something
is slightly different. Is tb_Agent.Agent_ID the primary key for this table?
If not, you could have dupes.