How to Lose Duplicates in Query

[Mottah]

This code works fine without the GROUP BY, as soon as i type it in and run the code access gives me an error msg: You tried to execute a query that does not include the specified expression 'date' as part of an aggregate function. But i need the group by to get rid of any duplicate email addresses. How can I tackle this?

SELECT emails.date, emails.email
FROM emails
WHERE emails.date<#11/1/2010#
GROUP BY emails.email
ORDER BY emails.date DESC;

[gershwyn]

You cannot include a field in your select list that isn't in your group by clause or part of an aggregate function. Your query is saying "show me the e-mail only once, but show me every date" - which can't be done.

If you're looking for just a list of e-mails, then drop the date from your select list. If you need the date included, you need to think about which date you want included. For example, you could use the Max() aggregate function to include the highest (most recent) date associated with each e-mail.

[NeoPa]

I think you need to get clearer in your mind exactly what you're trying to do. That way you can ask a question that makes more sense, or more accurately reflects what you want to know.

I'm guessing that you're not really looking for a GROUP BY query at all, but that you simply want to avoid any duplicates. In that case you need the DISTINCT predicate of the select clause. Something like :

SELECT DISTINCT
         [Date]
       , [email]
FROM     [emails]
WHERE    [Date]<#11/1/2010#
ORDER BY [email]
       , [Date] DESC;

[Mottah]

What I have is a list of buyers who buy tickets online once or many times on different dates. The system tracks the date of purchase against the email address. If the same email address buys on different days it is tracked as another sale. What I want to extract from my database now is the email address, no matter how many they bought on different dates.

[NeoPa]

Mottah:
What I want to extract from my database now is the email address, no matter how many they bought on different dates.

I wish I knew what you were trying to say here. I try to break it down into constituent parts, but none is individually coherent either.

1. What does each output record need to represent?
2. Which, if any, input records need to be discarded?
3. In which circumstances might an email address appear on more than one output line?
4. Most importantly, is this a new requirement or an attempt to clarify this question?

Please answer each question to allow us to proceed.

[Mottah]

1. Each output record is a ticket sale (made up of date and email address)
2. I need to discard repetitive dates for the same email address
3. An email address appears more than once if it's owner made more than one sale.
4. Attempt to clarify question.

NB: I tried using the code with DISTINCT you recommended, but realised it can still include duplicates. If the same email address buys on different dates.

[Rabbit]

Get the date out of your query when you're doing distinct.

[NeoPa]

Well answered Mottah (You'd be surprised how many members fail to answer the questions directly as you have).

However, I have some trouble with your answers to #2 and #3. Specifically, if according to #2, multiple records are discarded leaving only one record per email address, how can #3 make sense?

I'd also like some clarification on #1 if you can. Assuming #2 is correct, which [Date] value do you want to show in your results (First; Last; Avg; Min; Max; etc)?

[Mottah]

I found the help I needed. I had to remove the date from the query when doing DISTINCT. Because all i was really looking for was non repetitive emails no matter the date. Thanx!

But I have another question, now that I have gotten the email addresses without duplicates. I want to use the results (the unique email addresses) to group them by location.

I have another column in my table called location.

What query do i use?

Please assist. My deadline is today.

[Rabbit]

Put location in your query and group by it.

[NeoPa]

NeoPa:
I think you need to get clearer in your mind exactly what you're trying to do. That way you can ask a question that makes more sense, or more accurately reflects what you want to know.

Such a perfect illustration of the need to follow the advice I gave in paragraph #1 of post #3. A lot of time and effort wasted chasing around a question that never made proper sense in the first place.

I would give a minor variation on Rabbit's advice :
"Put [Location] in your SELECT clause and ORDER BY it."

[Mottah]

Thank you Neopa. I guess I did not ask my question clearly in the beginning, but now everything works perfectly!

[NeoPa]

I'm very pleased to hear it's working for you now Mottah.

You're new, so it's no surprise you don't get it right first time. What's important is that you understand there's a better way going forward, which I guess you do now. We look forward to more questions from you in the future.