Sorting, grouping and counting

[DonH]

I have 3 fields:

PatientID, deliverydate, ethnicity

Delivery date is for babies.

I need to come up with a count, by ethnicity, of how many deliveries there were.

Big problem. The guy who wrote this did not limit the input on ehtnicity so I have things like Cau, cau., cauc, and so on. So I can't just say "count how many are "CAUCASIAN""

What I need is for the count to be grouped by ethnicity and then output a report of each ethnic group and the number of deliveries sorted on ethnicity.

I've tried numerous ways but I seem to just keep getting the total.

[Ender]

I'd suggest fixing the bad data. The tough ones to fix are the mixed ethnicity entries (Black & White). You can find those records that have bad ethnicity entries by using something like "==cau" as the find criteria in the ethnicity field. Then replace the field contents using Replace.

Make sure you back up your file before using Replace because you can't undo if you make a mistake.

[DonH]

But how do I get a listing of all the ethnicities with a count for each?

[-Queue-]

If the field is indexed, enter Find Mode, put your cursor in the field, and press Ctrl-I (Cmd-I on Mac). It will show you a list of all unique entries for that field. You can select one, continue your find, and change the found set as needed.

[Ender]

You can summarize by ethnicity by creating a Sub-Summary by Ethnicity part on a list layout (remove the Body part). Put Ethnicity on this part and a Count summary field, and then sort by Ethnicity. Enter Preview Mode to see the counts, or print it.