totals query - group by email max of date
in [General]
|
From: ZenMasta on 9 Apr 2010 14:57 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 www.me(a)yahoo.com 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.
From: John Spencer on 9 Apr 2010 15:13 SELECT [First Name] , [Last Name] , Replace(S.[Email],"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 wrote: > 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 www.me(a)yahoo.com > 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. > >
From: ZenMasta on 9 Apr 2010 16:06 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.[email],"www.","") as [E_mail], [date], [IP] FROM [email-list] as S WHERE [date] = (SELECT Max([date]) FROM [email-list] as Temp ON Temp.[firstname] = S.[firstname] AND Temp.[lastame] = S.[lastname] AND Temp.[email] = S.[email]) Whever I try to save it would say syntax error in query expression [date] = (SELECT Max([date]) FROM [email-list] as Temp ON Temp.[firstname] = S.[firstname] AND Temp.[lastame] = S.[lastname] AND Temp.[email] = S.[email]) "John Spencer" <spencer(a)chpdm.edu> wrote in message news:OXRe8iB2KHA.556(a)TK2MSFTNGP04.phx.gbl... > SELECT [First Name] > , [Last Name] > , Replace(S.[Email],"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 wrote: >> 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 >> www.me(a)yahoo.com 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. >
From: KARL DEWEY on 9 Apr 2010 19:24 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. "ZenMasta" wrote: > 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.[email],"www.","") as [E_mail], > [date], [IP] > FROM [email-list] as S > WHERE [date] = > (SELECT Max([date]) > FROM [email-list] as Temp > ON Temp.[firstname] = S.[firstname] > AND Temp.[lastame] = S.[lastname] > AND Temp.[email] = S.[email]) > > > Whever I try to save it would say syntax error in query expression > [date] = > (SELECT Max([date]) > FROM [email-list] as Temp > ON Temp.[firstname] = S.[firstname] > AND Temp.[lastame] = S.[lastname] > AND Temp.[email] = S.[email]) > > > "John Spencer" <spencer(a)chpdm.edu> wrote in message > news:OXRe8iB2KHA.556(a)TK2MSFTNGP04.phx.gbl... > > SELECT [First Name] > > , [Last Name] > > , Replace(S.[Email],"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 wrote: > >> 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 > >> www.me(a)yahoo.com 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. > > > > > . >
From: ZenMasta on 16 Apr 2010 17:23
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. |