Distinct Command in Query

  • Thread starter Thread starter Union_98
  • Start date Start date
U

Union_98

Access 2000

Can you use the DISTINCT command when you have two tables
joined? All the examples that I can find show one table
and one field.

The two tables are linked by SSN field and in the second
table there is the possiblity that the SSN was used more
than once (multiple choices in one of the fields). In
other words, Table 1 shows Johnny, SSN#343-34-3456 and in
Table 2, that SSN#343-34-3456 has been Admitted (one
entry) and Enrolled (second entry). Both Admitted and
Enrolled are linked to Johnny by his SSN.

When I try to see if Johnny was Enrolled only, the display
always shows two rows for Johnny - one saying he was
Enrolled and one saying he was Admitted.

I used Select Distinct on Table 1 SSN and then tried it on
Table 2 SSN but it didn't make any difference.

Is there hope for me? *grin*

Debby
 
Okay - more to add. Distinct may not be what I need to
use. I've been playing and have found out that if I use
AND in my Where clause (instead of Or) I can get only
Admitted to display (instead of both Admitted and
Enrolled).

But I do have another query where I have to use Or in the
Where clause to retrieve those that have Completed GED.
Is there another way to get this query to work?

Here is the one that doesn't give me what I need.:

SELECT DISTINCT Client.SSN, Client.CounselorNum,
Client.LastName, Client.FirstName, Client.EBSomeHS,
Client.EBHSGrad, Client.EBGED, Client.EBCollege1,
Client.EBCollege2, Client.EBCollege3, Client.EBCollege4,
Client.EBPreHS, Status.Status
FROM Client LEFT JOIN Status ON Client.SSN = Status.SSN
WHERE ((Client.EBSomeHS)=True) OR ((Client.EBHSGrad)=True)
OR ((Client.EBGED)=True) OR ((Client.EBCollege1)=True) OR
((Client.EBCollege2)=True) OR ((Client.EBCollege3)=True)
OR ((Client.EBCollege4)=True) OR ((Status.Status)
="Completed GED")
ORDER BY Client.LastName, Client.FirstName;

It shows everything, not just "Completed GED" if
Status.Status has more than one entry for that SSN.

Thanks for your help! (Actually just trying to explain it
seems to be helping me. *grin*)
 
Back
Top