Count Distinct??

[Erin learningasigo]

I have a simple database with students. They each have a unique ID number. They each have a Country of Citizenship. On my layout I have Total Students as a Trailing Grand Summary. How do I also list Total Countries at the end of the report, so it will list total distinct countries? It should be about 87, and the total number of students 1173. Please try to reply in basic English, since I'm learning as I go and I'm self-taught. THANKS! ~Erin [color:"green"]

[-Queue-]

Create a summary field that is a 'Count of' your Country field. Then create a calculated number field of 1 / GetSummary( CountCountry, Country ), and another summary field that is a running total of the calculation field. Put this running total in your trailing summary and include the country field in your sort order.

See attachment for similar example. Note there are 11 unique countries, as the final record displays.

Country.zip

[Erin learningasigo]

Create a summary field that is a 'Count of' your Country field. Then create a calculated number field of 1 / GetSummary( CountCountry, Country ), and another summary field that is a running total of the calculation field. Put this running total in your trailing summary and include the country field in your sort order.

See attachment for similar example. Note there are 11 unique countries, as the final record displays.

Thank you! I did everything you said, and checked your attachment to make sure I matched, but my result is a question mark. Any other thoughts?? Thanks....~Erin

[-Queue-]

Is the calculation result set to number? Is Country the first field in your sort order?

[Erin learningasigo]

Is the calculation result set to number? Is Country the first field in your sort order?

Yes, Yes. Sorry - that was a dumb mistake. It just defaulted to a decimal so it wouldn't fit in the field. It is working beautifully. Thank you so much for your help! This is a great website and I plan to visit often.

[Ninja]

Ive been trying to use these methods with my database, its setup a little different though and i can't translate these to work in it.

It a distribution database. The first file "Orders" contains data on the delivery date and address of any delivery. Each delivery has a unique serial number.

Then there is the file "Items" this contains details of individual items which are included in each order.

The records in items are inputted through a portal in "Orders" and are related through the unique serial number.

Each item has an order number associated with it. In one order there may be upto 20 items, and these items may have one order number or 20 order numbers depending on how the deliveries have been requested.

The database is required to return the number of unique order numbers for each Order since this value has a charge associated with it.

I therefore need a field in "Orders" which contains the number of unique order numbers in the related records in "Items". This order number value must remain with the order for the purposes of invoicing.

At the moment i have made it so that the data inputter has to enter the number or unique order numbers simply because this is the only way i can keep the figure from changing as soon as the records are sorted differently or there are a different number of them in the given found set.

any ideas would be greatly appreciated, especially ones with example files.

Thank you

[-Queue-]

See if the attachment gets you moving in the right direction.

Orders_and_Items.zip

[Ninja]

I can't use it i'm afraid, i only have FM6!

[-Queue-]

This just isn't my week!

Here's the fp5 version.

Orders_and_Items.zip

[Ninja]

You're a Genius, It works perfectly and its so simple, only 2 relationships, i only need to add 2 Fields to what i already have. I cant believe its so simple! I even understand how it works!

[-Queue-]

Thanks, it's a nice technique for sure. You should be aware, though, that if you have an egregious number of related records, the Sum calc can slow down tremendously. You might therefore want to use a script to set a number field with the result of the Sum, instead of using a calculation field, when the order is set.