Expert: Scottgem - 10/30/2008

Question
QUESTION: Scott,
I send out an eNewsletter for my business and track what links my subscribers are clicking on by using a bulk e-mail vendor.  I'm building a database in Access that would neatly sum up each subscriber's "history."

I have our subscribers' profiles in one table and the past links they've clicked on in another.  Is there a way to run a query that will combine all of the duplicate profile info, sum total clicks, and ACCUMULATE all of the unique info (links & months).  Please see attached image; I've used Excel to illustrate my thoughts.  If this isn't possible, is there any other way you recommend I attack this task?  Thanks for your help!

Sincerely,
Ty

ANSWER: I'm not clear on what is duplicated. The links or what?

What you have is a classic relational setup with a one to many relationship. You create a query that joins the two tables on your subscriberID. You then use the report wizard to create a report that groups on subscriber and lists the links they've followed in the detail section.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott,
Thanks for your prompt response.  In the "Sample Click Data" table, a contact's email and name are duplicated because they've clicked on multiple links. If I create a query that joins the two tables by email address, this is what I receive (see new image).  I'm wondering if there is a way to sum up the number of a contact's total clicks, and also, "sum up" the links they've clicked on with the months they did it in.  For instance,  has 4 different records in the most recent image I've uploaded.  Is there a query I can run to "compress" his 4 records into 1 record (in other words, all of the websites he's clicks on would be in one field...or what I've attemped to illustrate in Subscriber History.xls in my first uploaded image)?

Sorry if I'm not explaining this clearly, but I truly do appreciate your help.  Let me know if you need further clarification. I don't know if what I'm trying to do is even possible in Access (without a report) so I'm just exploring my limitations.

Thanks!

Tyler

ANSWER: First, you can't do this in a query, but you CAN do it in a report as I outlined in my first response.

Second, in your click table, are you actually entering the e-mail and name data or is it being imported from info provided fromthe web site?

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: MS Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: The email address and name data is being imported from info extracted from our bulk email provider.

Answer
Ok, I suspected that. Because generally, you would not include both name and e-mail address in the link table. You would only have a foreign key linking back to the customer table.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: MS Office Access 2007 VBA