Crosstab query showing duplicate rows

[Aliasjeffturner]

Design View http://imgur.com/a/OitgN




Datasheet http://imgur.com/a/8bp9z


I want to provide a list of account numbers and see if those accounts have the RPSs (Restricted Product Set, essentially 1 of 400+ codes) I have listed in the criteria, 1, 142, 260, 280, 318, 373, 374, 376, 401.


I want the query to return a column for each account number i provide (in an excel linked table) and 9 rows, 1 for each RPS I have listed. I thought this was the point of a cross tab query, to be able to quickly evaluate data. If all the counts were in the same 9 rows, i could quickly see if an account is missing one of these RPSs. Instead, the query is returning 9 rows for EACH account number, which is no more helpful than if i just created a select query.


What am i doing wrong? This is my first attempt at a crosstab query. Thanks in advance for your help.

[ranman256]

The main query should run with no duplicated.
then the Crosstab query is based off that query.

[Aliasjeffturner]

I see... Well if i created a select query with the criteria of account number and RPS, it would bring back each RPS for each account. For example, if every account has RPS 1, there would be 5 entries for RPS 1. So since i have selected RPS as my row heading, I will get 5 rows for RPS 1?

Do you know any way to tweak the criteria to only have 1 row for each RPS, and then multiple 1s going across denoting each column/account that has that RPS? Would it change if i swapped the rows and columns? or is there something else i could use for the "value"?

EDIT: I'm looking for something like this: http://imgur.com/a/YkRfq

[aytee111]

The first query will show all the data you want to display. The crosstab query which runs off that will show one line for each row heading.