Need to Identify Missing Data

[umberger]

I am trying to find a way to identify records that are missing from my report. I have 30 schools that should have data input into our records management system each month. I have created a cross tab with the schools in rows and the date grouped by month in the column. The school is listed on the report only if a record was input for that month. I need all of the schools to show up or need some way to identify which schools are missing. I am a fairly new Crystal user so please help.

 

[Turkbear]

Hi,
Is more than one table involved?
If so, link the table with the School to the other table(s) with a LEFT-OUTER join..That way the school will show up even if no data for it is in the other table(s).

If just 1 table is involved and a school is not entered if no records were input, then you are out of luck in finding what is not there.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Actually, if just 1 table is involved, but there is a table available with all the schools listed, linking the one table with data to that School table could reveal which schools are missing:
SchoolNametable --LEFT-OUTER--> Data table



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[chelseatech]

We wrote a series of articles in our Crystal Reports newsletter on how to report on missing data. Download the back issues to see which suits you best.

I suspect the left join might be the best approach to see which schools have supplied no data. If a school has supplied some data, but hasn't done May yet, then a SQL expression with a Sub SELECT might be another approach

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[umberger]

Thank you for all the suggestions pointing me in the right direction. I am making progress.