SELECT DISTINCT not working 1

[BrenoAguiar]

HI folks,

I have this query that I want it to bring ONLY DISTINCT folio numbers and the SQL viw is like this:

SELECT DISTINCT guesttrack_clean.Folio, brochure.type
FROM brochure INNER JOIN guesttrack_clean ON brochure.homephone = guesttrack_clean.HomePhone
WHERE ((([BookingDate]-[datesubmit])>0))
ORDER BY brochure.lastname;

But this is still giving me duplicate values in the guesttrack_clean.folio .

Any help?
Thanks a lot

 

[threadgills]

Obviously you have more than one record for each one...it seems to me that you may have 2 different values in both records. I.e., brochure.type could equal "this" in one record and "that" in another.

 

[BrenoAguiar]

Yes. But Can I Use the DISTINCTROW instead, in order to eliminate the ROW, based ONLY on the guesttrack_clean field with no duplicates. In other words, disregarding the brochure.type beeing "this" or "that" ?

 

[hkaing79]

I believe what threadgills is suggesting is to just remove brochure.type.

 

[BrenoAguiar]

I did that. But I have a report that is attached to the query and I need that info to show on the report, but not as a sort item. I created a another query, reading off this one WITHOUT brochure.type, just the guesttrack_clean.Folio (which brings the right number of records withou dups) but if I add another field on the SELECT, here comes the duplicates again, even reading out off the query that has only the records with no dups.

 

[hkaing79]

If you had the following duplicate:
Folio, Type
1, 1
1, 2

What result are you looking for?
1, 1
1, 2
or just 1

You can use Min() and Max() to capture a single Folio, and then use Min(Type) or Max(Type) to take only one of the Type results.

 

[hkaing79]

I meant to say...
You can use Min() and Max() to capture a single Folio, by using Min(Type) or Max(Type) to take only one of the Type results.

 

[BrenoAguiar]

Good. Thanks for that. what I needed was the result "just 1" that you mentioned and I'm using Max().