I have a table that has three columns: Emails, Channel, and Date. There are multiple duplicate values of emails. I would like a query that yields only unique emails(no duplicates), channel, and earliest date.
Example:
Email Channel Date
[email protected] A 1/12/13
[email protected] B 3/1/13
[email protected] A 4/3/13
[email protected] B 5/13/13
[email protected] A 9/13/13
I would like to see this:
Email Channel Date
[email protected] A 1/12/13
[email protected] B 5/13/13
Try this.
The table name here Emails
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
It is important to note that using First as a function will bring you the first entry in the data base table and not necessarily the earliest date. For example if the order of data in your table for Test is reversed, then the result would show A and 5/13/13.
Additionally, the term DATE is a reserved term in Access and should not be used a field name. It could Access some confusion. You would be better served to use something different; eDate, Date1, or some variation.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
What should I use then instead of FIRST?
Actually there should be MINOriginally Posted by AlphaSkidz
See code below
Please Login or Register to view this content.
Is there a chance that since we are using FIRST for channel that we could be getting the wrong data?
For Instance:
[email protected] B 12/13/13
[email protected] A 9/13/13
Would the query show it as [email protected] B 9/13/13 (since B is the first in the table?) I would like to be certain that with the query I use I will get
[email protected] A 9/13/13
Your query will show:
[email protected] A 1/12/13
[email protected] B 5/13/13
This what you want. Isn't it?
I need the earliest date associated with the email and the corresponding channel. I'm afraid that what the query is doing is just picking the first instance of the channel, which is incorrect. The query is correct in pulling distinct emails and the earliest date. Please see examples below, and let me know if it adds some clarity.
In this scenario I would need [email protected] A 9/13/13
[email protected] B 12/13/13
[email protected] A 9/13/13
In this scenario I would need [email protected] A 1/12/13 and [email protected] B 5/13/13
[email protected] A 1/12/13
[email protected] B 3/1/13
[email protected] A 4/3/13
[email protected] B 5/13/13
[email protected] A 9/13/13
Please see query in my post # 5
Min(Emails.Date) AS [Date] it uses MIN function to get the earliest date.
I know, I'm worried about the channel. Using FIRST on channel is incorrect since it will pull the first record in the table rather than the channel that matches up with that email and date. Does that make sense?
For [email protected] Channel B corresponds with the earliest date 5/13/2013
For [email protected] Channel A corresponds with the earliest date 1/12/2013
This is what this query does.
What would your query pull in this example?
I'm afraid your query will pull Test@gmail C 1/30/13 instead of Test@gmail B 1/30/13
Test@gmail C 12/1/13
Test@gmail A 3/23/13
Test@gmail B 1/30/13
What would your query pull in this example?
I'm afraid your query will pull Test@gmail C 1/30/13 instead of Test@gmail B 1/30/13
Test@gmail C 12/1/13
Test@gmail A 3/23/13
Test@gmail B 1/30/13
Have you even tried to ran this query? If yes, what problems did you find? If not, why not?
I have ran the query, and it pulls the incorrect channel. It will always pull the first channel in the table. It pulls Test@gmail C 1/30/13 instead of Test@gmail B 1/30/13
Test@gmail C 12/1/13
Test@gmail A 3/23/13
Test@gmail B 1/30/13
All I have is what you provided in you original post. Whatever you have in post #15 really doesn't explain anything except that "It pulls Test@gmail C 1/30/13 instead of Test@gmail B 1/30/13" If there any criteria should be used for channel, it has never been mentioned.
I don't know how else to explain it other than the way I have been. I have mentioned multiple times that the FIRST command is not correct to use with the channel field since it will pull the first record in the channel column. I need the query to look at the earliest date(which it already does with the use of MIN) and also pull the same channel that is tied to that email and date. That is why I have been giving you examples on each of my posts.
I got it figured out. I modified your query and took "channel" out of the select statement. I then made another query that referenced the main table along with the query that was made. I left outter joined on email and date, and chose channel. That way it would pick the correct channel rather than picking the first one in the table.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks