CROSSTAB TO COMPARE CURRENT YEAR VS PREVIOUS YEAR

[merv02]

Can someone help me please, I have an Access Dbase and I would like to create a crosstab that will compare the selected year to the prior year using CRX1 R2.


This is a sample of the data
ID year semester grlvl school_name sex Exam Score EXAM
000-000-002 2006 Fall 2006 7 SCHOOL A Female 1 ENG
000-000-002 2006 Spring 2006 7 SCHOOL A Female 1 ENG
000-000-001 2007 Spring 2007 7 SCHOOL A Male 4 ENG
000-000-001 2007 Summer 2007 7 SCHOOL A Male 3 ENG
000-000-002 2007 Fall 2007 8 SCHOOL A Female 1 ENG
000-000-002 2007 Spring 2007 8 SCHOOL A Female 1 ENG
000-000-002 2007 Summer 2007 8 SCHOOL A Female 4 ENG
000-000-001 2008 Fall 2008 8 SCHOOL A Male 3 ENG
000-000-001 2008 Spring 2008 8 SCHOOL A Male 3 ENG
000-000-001 2008 Summer 2008 8 SCHOOL A Male 3 ENG
000-000-002 2008 Fall 2008 8 SCHOOL A Female 1 ENG
000-000-002 2008 Spring 2008 8 SCHOOL A Female 1 ENG

sample result
EXAM 2007 2008 Grand Total
ENG 5 5 10
Grand Total 5 5 10

 

[Madawc]

Have you tried creating a crosstab? I can't see any obvious problem with what you want. Just try it and see what comes out.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[merv02]

Sorry I just realized I didnt' explain properly. I can create a crosstab to compare one year vs the another. But I need to create a crosstab that will compare the same students from a selected year to that same set of students data from the previous year. So the studetns has to have data in the current and previous year.

 

[Madawc]

You can show two sets of values in the same crosstab.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Insert a group on studentID in the main report. Then create a formula:

//{@bothyrs}:
if distinctcount({table.year},{table.studentID}) = 2 then 1
//assuming your summary is meant to be a count

Add this as your summary field in the crosstab, using sum, NOT count, as your summary. Also go to report->selection formula->GROUP and enter:

distinctcount({table.year},{table.studentID}) = 2

This assumes you already have a record selection formula (report->selection formula->record) like this:

{table.year} in {?year}-1 to {?year}

-LB

 

[merv02]

Thanks Ibass I will try that