Help with cross tab queries with aggragate columns.

  • Thread starter Thread starter Sasha
  • Start date Start date
S

Sasha

I need help with a cross tab query. Here are my results:

ID Year Deduction Earnings----Col names

DC2 2003 10714.28
DC2 2003 322.90
DV 2003 2500.00
DV 2003 153.50
IT 2002 5408.00
IT 2002 986.98

Columns Deduction and Earning or aggregate columns getting the total sum of
the field totals.
Based on this cross tab query. How can I get a final result, as such:

ID Year Deduction Earnings

DC2 2003 322.90 10714.28
DV 2003 153.50 2500.00
IT 2002 986.98 5408.00

If you need more information, please reply and I'll be happy to supply as
many details as needed to resolve this issue.

Thanks,

Sasha
 
It isn't clear what your original fields are. Is your first example the real
fields (or their sums)? What is the SQL of the first example. I looks to me
that you could just create a totals query of your first example and Group By
ID and Year and Sum Deduction and Earnings.
 
Here is my Qry text;

TRANSFORM Sum(dbo_EarnDed.MtdEarnDed00) AS SumOfMtdEarnDed00
SELECT dbo_PayGroup.PayGrpId, dbo_EarnDed.CalYr
FROM dbo_PayGroup INNER JOIN (dbo_EarnDed INNER JOIN dbo_Employee ON
dbo_EarnDed.EmpId = dbo_Employee.EmpId) ON dbo_PayGroup.PayGrpId =
dbo_Employee.PayGrpId
WHERE (((dbo_EarnDed.EDType)="e")) OR (((dbo_EarnDed.EDType)="d") AND
((dbo_EarnDed.EarnDedType)="c")) GROUP BY dbo_PayGroup.PayGrpId,
dbo_EarnDed.EarnDedType, dbo_EarnDed.CalYr
PIVOT dbo_EarnDed.EDType;


Thank you.

Sasha
 
Back
Top