1

I am working in MS Excel 2007 with data regarding a series of forum posts. Each post is represented by a row of data that includes a unique ID for each post, the unique ID of the user that posted it, the unique ID of the discussion as well as several columns of descriptive variables. One of these descriptive variables is whether or not the post supports the discussion (is a pro or a con). There are several thousand users and several thousand posts.

1. I want to find the instances where users have made more than one post in the same discussion. (Probably a few dozen instances)

2. I want the instances in which users have posted both a "pro" and a "con". (Probably a handful)

Question number one is the important one. The second will probably be easy once I've figured out #1 since the instances are few.

Alternatively, how could I accomplish this with Access (which I am much less familiar with)?

1
  • I figured Access might be the best tool for this and gave that a try. I am however an absolute beginner in Access, so I soon decided to focus more on Excel. In Excel, I am trying to set it up in pivot tables, which admittedly I have little experience with. But I guess you can't go through life without pivot tables, so learning it is worth the effort. I'm getting closer, but still haven't figured it out.
    – Stoney
    Nov 14, 2012 at 17:27

1 Answer 1

2

This is easier in Access than in Excel.

In Access, import your data from Excel using the import Wizard. Once your data is in a table, create a new query, go to SQL view, and use a query like the following to find users posting more than once in a single discussion:

SELECT DiscussionID, UserID, Count(PostID) as Number_of_Posts FROM Sheet1
GROUP BY DiscussionID, UserID
HAVING Count(UserID)>1;

When executed, this will return in table form the DiscussionID, the UserID, and the number of posts by that user in the discussion for each who has more than one post. Of course, you'll have to change the table and field names to match your database.

To find the users who have posted both pro and con in a single discussion, you can use the following query:

SELECT DiscussionID, UserID, SUM(Number_of_Posts) as Posts
FROM (SELECT DiscussionID, UserID, Pro_Con, Count(PostID) as Number_of_Posts FROM Sheet1
      GROUP BY DiscussionID, UserID, Pro_Con)
GROUP BY DiscussionID, UserID
HAVING Count(UserID)=2;

This is a bit of a hack on my part; I'm sure there has to be a more efficient query to do the job, but nonetheless, this will work.

1
  • Thank you! It all worked and you nearly guessed the names of the fields correctly.
    – Stoney
    Nov 14, 2012 at 19:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .