Query Select Distinct - Is there a nested Select Distinct?

[pjdube]

Dear All,



Thanks for helping me out before. There is one thing that I am stumped on.

The below query works great but one thing, it does not filter out the duplicates.

Sometimes I will have TWO lessons come in from one person and I just want to show the person once, or just show the latest
lesson from that person when it is the same day. I am thinking that somehow I will have to have a separate SELECT DISTINCT
statement in this query.

Here is the query I have:


SELECT DISTINCT TM.[Full Name], TM.[Lesson In], TM.[Date In], TM.Course, TM.Section, ED.Sections, [Section]/[Sections]*100 AS [Pct Done]
FROM TM INNER JOIN ED ON TM.Course = ED.Course
GROUP BY TM.[Full Name], TM.[Lesson In], TM.[Date In], TM.Course, TM.Section, ED.Sections
HAVING (((TM.[Date In])=[Forms]![Logging]![WEFilter]));

Any pointers?

Phil

[June7]

Same name with 2 lessons are 2 DISTINCT records so both will return since they are not duplicate records.

If you want the latest lesson for each name for each day, that will likely involve nested TOP N. Review http://allenbrowne.com/subquery-01.html#TopN

[CJ_London]

using distinct with group by won't do anything as in this context they are the same thing. And to show the latest you need something that will identify it - might be a time element, might be an ID, might be something else. Last means nothing without an order. If you have two records with the same date what decides which is the last one?

Does your query actually run? you say it does but you are not grouping on your pct done calculation.

Suggest you provide some example data and the result you want to get from that example data

[pjdube]

Ok, to answer June 7 first,

Yes tracking! Thanks for the link. I will look into this!

Ajax, to answer your question, what decides which is the last one, is the Lesson Number. Here is some Example Date:

Full Name	Lesson In	Date In	Course	Section	Sections	Pct Done
Jones, John	Yes	07-May-20	Blah Course	1	13	7.69230769230769
Jones, John	Yes	07-May-20	Blah Course	2	13	15.3846153846154
Aungst, Jack	Yes	07-May-20	Blah Blah Course	6	11	54.5454545454545
Aungst, Jack	Yes	07-May-20	Blah Blah Course	7	11	63.6363636363636
Bowlings, Jill	Yes	07-May-20	Blah Blah Blah Course	2	12	16.6666666666667

From the example above you can see that I still end up with "duplicates" which is fine for another
query I have that counts the number of lessons sent it. But this query I just want to have one instance
of the person who sends in their lesson, preferably the latest lesson (i.e. John Jones' record being on Section 2).

I hope this clarifies.

Phil

[pbaldy]

You will get unique combinations of the fields in the GROUP BY clause. Since Section is one of them, you get a record for each section. In other words, they aren't "duplicates" since they have different section values. Either drop that field from the query or in design view change it to Max, Min or whatever as appropriate to your needs.

[pjdube]

Paul,

Thank you! I knew it would be something very easy! I did as you instructed, I just changed it to Max and it works properly now.

Thanks million.

Phil

[pbaldy]

Happy to help Phil!