Excel Pivot Table - how to arrange fields to sum a field while ignoring duplicate rows from another unrelated field

Question

I hope someone can help me figure out what I'm doing wrong here - maybe it's my lack of Pivot table knowledge or even how the data is being modelled.

I am creating a report from an MS Access database query. I have simplified the data for the purpose of this question.

The table produced in Excel after connecting to the Access query looks like this:

The main point here is that even though Bob has only completed Survey 1 once, there are duplicate rows for each question answered because we are also bringing in data on what pets he has.

The Pet table is a M2M relationship and is completely separate to the survey questions & answers. However, the report needs to show not only the aggregate survey score for each client, but also how this breaks down against Pets. For example, how many people score mainly 4s for each question that have Dogs versus Cats.

Each question has an Answer Score between 1 to 4.

So I need to create a report that can show the client's aggregate survey score as 12 (and not 24), and then bring in 'Pets' (and other M2M client information) to analyse the relationship between Answers and other information about the client (E.g. do people with dogs tend to answer with category '4' answers more often than those with cats?).

Any thoughts on how I can best achieve this would be so helpful.

Answer 1

If you really have to use a pivot table.. Using "Data Model" option, use "Distinct Count" on the Pet field.

Alternatively, use "Data", "From Other Sources", "From Microsoft Query", "Excel Files", "OK", navigate to the opened file. Select the data source and required columns and create a new query: SELECT DISTINCT Sheet1$.Client, Sum(Sheet1$.Score) AS 'Sum of Score' FROM [file path\file name].Sheet1$ Sheet1$ GROUP BY Sheet1$.Client