Crosstab qry not adding up

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

I've built a crosstab qry for a tenant sales database based on a select qry,
the original select query works (adds up correctly) ive checked by sorting
and adding totals to columns, but the crosstab is tripling the amounts in
each total field both for each month and the totals - i'm thinking its
something to do with the fact there are three different income types (rent,
cleaning, other) because the rows where the tenant has only one income type
are correct

this is the code

TRANSFORM Sum(SalesToTenant.[Amount]) AS SumOfAmount
SELECT SalesToTenant.[Company], SalesToTenant.[IncomeType],
Sum(SalesToTenant.[Amount]) AS [Total Of Amount]
FROM SalesToTenant
GROUP BY SalesToTenant.[Company], SalesToTenant.[IncomeType]
PIVOT Format([Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 
You did not post the the original select query SQL. That might be the
problem when it feeds the crosstab.
 
Back
Top