totals query - group by email max of date

[ZenMasta]

I'm trying to export our email list and I'm having a problem.

I'm working with 5 fields
first name, last name, email, date, ip

I tried to group by email and max of date, but I still saw duplicates. So I
also tried to max of IP and then I did not get duplicates. that's nice but
not what I was expecting. When I decided to max of date I was assuming that
I would get the IP for the most recent order, but now that I have to max of
IP also I don't know what I'm gonna get.


On a side note and somewhat unrelated. sometimes our customers are not very
tech savvy so when they enter their email address they put [email protected]
o whatever. I realize when I export this to excel I can find replace www.
but it would be handy if I didn't have to touch the file after exporting.

 

[John Spencer]

SELECT [First Name]
, [Last Name]
, Replace(S.,"www.","") as [E_mail]
, [Date]
, [Ip]
FROM [SomeTable] as S
WHERE [Date] =
(SELECT Max([Date])
FROM [SomeTable] as Temp
ON Temp.[First Name] = S.[First Name]
AND Temp.[Last Name] = S.[Last Name]
AND Temp.[Email] = S.[Email])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ZenMasta]

Thanks for that John.
I'm having trouble understanding how to replace my real fields with yours.
Maybe I shoulda pasted my query in the first place

SELECT customers.firstname, customers.lastname, customers.email,
opt_in_list.order_date, opt_in_list.customerIP
FROM customers INNER JOIN opt_in_list ON customers.id = opt_in_list.id;
queryname = email-list

So wher eyou had SomeTable I put email-list and for everything else (except
E_mail) I replaced your names with my real ones and came up with this

SELECT [firstname], [lastname], Replace(S.(e-mail address removed)

 

[KARL DEWEY]

Try these two queries --

qryLast_Order_Date --
SELECT opt_in_list.id, Max(opt_in_list.order_date) AS Last_Order_Date
FROM opt_in_list
GROUP BY opt_in_list.id;

SELECT customers.firstname, customers.lastname, customers.email,
opt_in_list.order_date, opt_in_list.customerIP
FROM (customers INNER JOIN qryLast_Order_Date ON customers.id =
qryLast_Order_Date.id) INNER JOIN opt_in_list ON qryLast_Order_Date.id =
opt_in_list.id
WHERE opt_in_list.order_date = qryLast_Order_Date.order_date;

--
Build a little, test a little.

Thanks for that John.
I'm having trouble understanding how to replace my real fields with yours.
Maybe I shoulda pasted my query in the first place

SELECT customers.firstname, customers.lastname, customers.email,
opt_in_list.order_date, opt_in_list.customerIP
FROM customers INNER JOIN opt_in_list ON customers.id = opt_in_list.id;
queryname = email-list

So wher eyou had SomeTable I put email-list and for everything else (except
E_mail) I replaced your names with my real ones and came up with this

SELECT [firstname], [lastname], Replace(S.(e-mail address removed)

 

[ZenMasta]

Maybe we should start over because I'm not sure any of this is really
working out.

table1 has these fields
customer_id
customer_firstname
customer_lastname
customer_email

table2 has these fields
customer_id
opt_date
customerIP

I really wish this was originally designed with one table in the first place
but it wasn't so this is what I'm stuck with.

 

[jenny molina]

Maybe we should start over because I'm not sure any of this is really
working out.

table1 has these fields
customer_id
customer_firstname
customer_lastname
customer_email

table2 has these fields
customer_id
opt_date
customerIP

I really wish this was originally designed with one table in the first
place but it wasn't so this is what I'm stuck with.

 

[silvio pizzorni]

Maybe we should start over because I'm not sure any of this is really
working out.

table1 has these fields
customer_id
customer_firstname
customer_lastname
customer_email

table2 has these fields
customer_id
opt_date
customerIP

I really wish this was originally designed with one table in the first
place but it wasn't so this is what I'm stuck with.