Record Inflation?

[neilmcdonald]

Hi,

I am reporting on a SQL database using Crystal V9. The report contains two tables, one for staff records (MASTER) and one for staff absence (ABSENCE). The report is grouped by location, division and staff no (all from the master table).

My problem is that each employee may have more than one absence record, and because of this, the subtotal of the headcount is multiplied by the number of absence records.

I've tried to get round this using running totals, but to no avail.

I gather that this is not an uncommon problem, but I can't seem to find a solution in the forums that fit this scenario.

Any help you can offer would be greatly appreciated.

 

[lbass]

If you are looking for the count of employees per location and division, you can right click on {table.employee} and insert a summary->distinct count. For other calculations, you might need to use running totals. For specific help, you should provide some sample detail level data and also a sample of the results you would like to see.

-LB

 

[Crystalyzer]

I had a similar one to many issue where I discovered that the information was being replicated as many times as there were links between the data. (Ex. Five positions and two stock certificate caused 5 x 2 = 10 times the amount to occur). I got around this by first using sub reports to pull in the data for each table and then created three formulas.

1st Summary of the replicated information.
2nd Distinct count of the items causing the replication.
3rd Summary / Distinct count which gave me the correct totals.

I know this is all "talking in the air" but hopefully it will shed some light on the process of thought you might need to use in considering your data and reporting needs.

Lloyd