473,324 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to Lose Duplicates in Query

5
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT emails.date, emails.email
  2. FROM emails
  3. WHERE emails.date<#11/1/2010#
  4. GROUP BY emails.email
  5. ORDER BY emails.date DESC;
Mar 23 '11 #1

✓ answered by 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."

12 2056
gershwyn
122 100+
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.
Mar 23 '11 #2
NeoPa
32,556 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.          [Date]
  3.        , [email]
  4. FROM     [emails]
  5. WHERE    [Date]<#11/1/2010#
  6. ORDER BY [email]
  7.        , [Date] DESC;
Mar 23 '11 #3
Mottah
5
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.
Mar 23 '11 #4
NeoPa
32,556 Expert Mod 16PB
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.
Mar 23 '11 #5
Mottah
5
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.
Mar 23 '11 #6
Rabbit
12,516 Expert Mod 8TB
Get the date out of your query when you're doing distinct.
Mar 23 '11 #7
NeoPa
32,556 Expert Mod 16PB
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)?
Mar 23 '11 #8
Mottah
5
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.
Mar 24 '11 #9
Rabbit
12,516 Expert Mod 8TB
Put location in your query and group by it.
Mar 24 '11 #10
NeoPa
32,556 Expert Mod 16PB
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."
Mar 24 '11 #11
Mottah
5
Thank you Neopa. I guess I did not ask my question clearly in the beginning, but now everything works perfectly!
Mar 25 '11 #12
NeoPa
32,556 Expert Mod 16PB
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.
Mar 25 '11 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Razarr69 | last post by:
I have a ASP application that I am using for my company and it is currently running on a Windows 2000 server box with IIS 5. The code works perfectly there (except for handling large files - due...
14
by: tshad | last post by:
I posted this on the asp.net group, also. I wasn't sure whether this was an asp.net problem or a javascript problem. I have a page that was originally created from a program I found on the net...
33
by: n00m | last post by:
import socket, thread host, port = '192.168.0.3', 1434 s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM) s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM) s2.connect((host, 1433))...
1
by: Avi | last post by:
Hi All. This code works very fine in Firefox but not in I.E. Can anybody help me out? it gives ... "Unknown Runtime Error" in I.E. This code ... Stores N*2 Matrix at Client Side & provide the...
2
by: Rico | last post by:
Hello, I have a web application that I developed in ASP.NET on one machine and I'm trying to deploy it on a Windows 2003 Server. The application runs fine on the development workstation...
19
by: pkirk25 | last post by:
I wonder if anyone has time to write a small example program based on this data or to critique my own effort? A file called Realm List.html contains the following data: Bladefist-Horde...
2
by: .spider | last post by:
Hi, This code emails the contents of 'TextBox1' and 'TextBox3' to rcv@domain.com.. this code seemed to be working fine in debug mode and DID mail rcv@domain.com However when i uploaded the website...
3
by: smartic | last post by:
I'm having problem with this code in firefox but in the Internet Explorer Browser works fine on it what is the wrong in my code ? <html> <head> <title>Like_mail</title> <script...
5
by: nickarnold | last post by:
Hi, this is my first time posting a question, so I apologize if I goof up or do not provide enough information. I am trying to implement a simple countdown script that I grabbed from Dynamic...
1
by: HH | last post by:
To append a single record, without getting a warning if a record already exists, I found out the following syntax works fine as Query in MS Access: first create a simple 'products' table... ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.