newbie question here:

How do I count unique values of a field in a subset of entries?

To be specific, I work at a homeless shelter (you might guess that we don't have any money for IT help). My database tracks admissions. Currently I have a single table with the characteristics of an admission, including client ID, SSN, dates of admission and discharge, etc. Clients are often admitted multiple times in a given date range, so multiple admissions may reference the same client.

I may need to break it into two tables- one for clients, one for admissions. Whether or not I do that, I can't figure how to solve my problem.

I want to ask how many unique clients were in the shelter in a given date range. Clients may come for 2 days, leave for 4, come back for 5. Thus they may have many admissions in the same quarter or month or year (or whatever I'm reporting on).

In SQL the distinct function would easily solve my problem. Considering I got Filemaker because Open Office Base was too hard, I feel a little nostalgic.

I'm such a newbie, but forgive me! I'm a social worker.

Thanks for any help you can offer.

Edited January 3, 200917 yr by Guest

If you need this in a report produced from the Admissions table, then read:

http://fmforums.com/forum/showtopic.php?tid/196904/

Otherwise, you could just find the admissions within the range, and call the Go to Related Record [ Clients ; Match found set ] script step - that would land you on a found set of Clients for the same range.

Thank you so much! I think that's exactly my question.