Duplicates not added in a Cross Tab Query

[WildlyHarry]

I have a table that contains transactions amounts, transaction codes, and
dates. I have created a Cross Tab query that that has the Transaction Code
and the Row Heading, the Date as the Column heading, and the Transaction
Amount as the value (summed). My goal is a to produce a query that shows the
Sum Transaction amount by code and date. The issue I have is that when I
have a duplicate transaction amount on the same day, which is very frequent,
the Transaction Amount does not sum all of the duplicates. If the amounts
are different, they are added. If they are the same, the query only displays
in the first one from the table. How can I get it to sum all transactions
for that date and code regardless of amount? Thanks in advance for you help.

 

[KARL DEWEY]

Try a totals query to feed your crosstab query.

 

[WildlyHarry]

"Totals Query"? I am not familiar, could you please eloborate?

 

[KARL DEWEY]

Create a select query and in the design view click on the icon that looks
like a funny 'E' and is the Greek symbol epsilon. In the grid for
Transaction Amount change Group By to Sum.
When the query is run it will give an alias of 'SumOfTransaction Amount' so
you need to edit the crosstab query for the totals query and the new name of
the field.

 

[WildlyHarry]

Thanks, I figured that out after a while.

Create a select query and in the design view click on the icon that looks
like a funny 'E' and is the Greek symbol epsilon. In the grid for
Transaction Amount change Group By to Sum.
When the query is run it will give an alias of 'SumOfTransaction Amount' so
you need to edit the crosstab query for the totals query and the new name of
the field.