Single Occurance Count(Count of unique records?)

[ctls]

Hi,

I've been thinking about this problem for a while and I can't think of a way to solve it on my own!!

I want a report grouped by department with a count of the number of students ID's in the department. Currently my records have multiple entries of student IDs. For example:

Department of Education:

295321

295321 (same as above!)

456372

Department of Science

342133

342133 (same as above)

675432

Currently my report looks like this:

Department Count

Department of Education 3

Department of Science 3

I made a summary field on student ID. And this gives me a count of 3 under Dept of Education and 3 under Dept of Science, which is wrong. What I am looking for is the count = 2, under each department as the student IDs are repeated. How can I use the summary field and calculate the number of unique records instead of just the number of records? Is there a work around on this??

Thanks for looking into this.

CTLS

[transpower]

But why would you want to have records in the table with the same studentID? Presumably each record should have a studentID field, with a unique value, and a departmentID field, a value list from the department table. Then your summary count function would work. The tables would be related by departmentID. The summary count field from the student table would be placed on the department layout to get the total number of students in that department.

If you cannot do the above, then write a script to sum the unique values into global fields. You would have to sort the found set by studentID and keep track of the last value to compare with the current before adding to the count.

[Ender]

CTLS,

It's been a while since I've done this, but I think you'll need a GetSummary function to get this to work:

Count of StudentID (summary) = Count of StudentID

Student Count by Department (calculation, number result) = 1/GetSummary(Count of StudentID; Department)

Summary Count of Student (summary) = Total of Student Count by Department

Put the Summary Count of Student field in the Sub-Summary by StudentID part, and sort by StudentID.

[transpower]

Another method would be by ODBC using the SQL query COUNT (DISTINCT StudentID).

[ctls]

I dont have ODBC set up and I cant do away with the duplicates of Student IDs as they are not really duplicates.

And Ender, I didnt get it workign with your suggestion. I could be creating the report wrong!!

I'll explain my task a little more. I have a Registration table.

The fields are "Student ID" and "Event Code". Each record is a unique combination of Student Id and Event Code. So, a "student ID" can appear more than once under different "Event Code"s.

There is another table "People" which has "Client ID" and "Department". This Registration and People tables are related by Student ID = Client ID. (The 'student ID' is like a subset of 'Client Id')

I want a report wih a count of Students from Each department. That is, I need a report grouped by 'Department'(from People) and a count of 'Student ID' under each department. As I explained in the first mail, this report gives me repeated 'Student ID' s under each department as there are multiple entries of the 'Student ID' in Registration table(under different 'Event Code's).

How I did this is, created a report based on 'Registration' Table, grouped data with 'Department' and gave subtotals with summary field 's_StudentID'. This summary field is a count of 'Student ID'.

So, what I want is something like this:

Department name :

Dept of Science

Student ID

123

123

345

Count 2

Presently I am getting a count of 3, though student Id '123' appears twice.

Anyways, Thanks Ender and Transpower.

CTLS

[RalphL]

Here is a little trick I saw somewhere recently.

Make a new columnar layout sorted by Student ID. Put page number on report. Put StudentID in Sub-summary part. Delete body. Set the sub-summary to page break before each occurance. Use the the following script:

Go to Layout [ "Layout #2" ]

Sort Records [ Specified Sort Order: Unique::SudentID; ascending ] [ Restore; No dialog ]

Enter Preview Mode

Go to Record/Request/Page [ Last ]

Set Field [ Unique::Count; Get ( PageNumber ) ]

Enter Browse Mode

Go to Layout [ original layout ]

I wish I could give credit to the person who came up with it.

[ctls]

Hey Ralph,

Thanks. I got a unique count by using a pattern count using the calculation:

PatternCount(ValueListItems(Get(FileName);"Valuelistname");"

[Ender]

I was close before. This should work:

Count of StudentID (summary) = Count of StudentID

Student Count by Student (calculation, number result) = 1/GetSummary(Count of StudentID; StudentID)

Summary Count of Student (summary) = Total of Student Count by Student

Then put the Summary Count of Student in a Sub-Summary by Department part.

[ctls]

Hey Ender,

This didnt work. The " Summary Count of Student" field remains empty. It has no value in it!

CTLS

[Ender]

You should be sorting by Department and StudentID.

Check out the attached demo.

SDMS.fp7.zip

[ctls]

Hey Ender,

I am working on a Macintosh and I am unable to download the attachment. Is there another way you could mail it to me??

Thank you.

CTLS

[Ender]

Too bad your version of Expander can't handle .zip files. But here it is in .sit format.

[T-Square]

Given your terminology, I am assuming that you're using v. 7...

I may be wrong, but I think your underlying structure could be modified to give you precisely what you want WITHOUT writing strange formulas.

As I understand it, you have a People table that includes more than just students, and that a student in your system is defined as "Any Person who has a related Registration." Yes?

If so, it would seem to me that you could create a new table occurrence (in the define relationships tab of the design database window) for your People table, call it "Students", and link it to the registrations table (ClientID = StudentID). Then, build your report on the Students table occurrence, and it should properly count only students.

This should work because in the People table, each student has one entry, and the link to the Registration table should strip out any people who don't have registrations.

I prefer this sort of solution because it doesn't require any coding or scripting.

HTH.

David

[Ender]

Hi David,

True, that will work if a student can only be registered for classes from the same department. But I think the report will need to be run from the Registration table if a student could have classes from more than one department. This means using strange formulas to calculate the counts.

You might also want to report on only those registrations from a certain period. To do this in the Student table, you'd need some strange relationships to filter the registrations for the desired period. If the report is run through the Registration table, you can search for a specific period pretty easily.

If you're based in the Student table, you may also have trouble listing the classes that a student is registered for. Portals can do this for viewing, but they don't print well.

[T-Square]

Ender--

In the circumstance you describe (where a student might be enrolled in classes from more than one department), the Department attribute really applies to the COURSE, and not the Student. Therefore, the data structure should be built such that a **class** is assigned to a department.

Taking this into account, along with what I expect are other needs in such a system, you need:

People

ID

DepartmentID (to identify affiliation of people, linked to PersonDept TO)

<etc>

Course

ID

DepartmentID (to identify Course affiliation, linked to CourseDept TO)

<etc>

Department

ID

<etc>

Registrations

StudentID (linked to Students TO)

CourseID (linked to Courses TO)

<etc>

Then, you could build reports that give you all sorts of useful information:

* Classes Taken by a Student (built on the Students TO)

* Enrollment for a Given Class (built on Registrations)

* Courses offered by Department (built on Courses)

Adding attributes to the different Objects (EnrollDate in registration, CourseStart CourseEnd in Courses, etc) allows you to build even more elaborate and useful reports.

Having made a course management system built on a different method of database Access, I can tell you that this was the general approach I ended up taking. In that system, the client was able to create a course schedule, output it for print or web viewing, enroll students in courses, view and print course rosters, view a student's course history--in other words, all those goodies.

David

[Ender]

It sounds like you didn't see my attachment, which has the structure you describe, but I think we're on the same page.

[stash]

Solution with three fields and two script lines

-----------------------------------------------

I've figured out one simple solution.

If we want to count unique values of many records, then my idea was to store these records, all of them, to the one field(Field_01_Array). Once we have all records in one field, then we can do with them whatever we want.

Three fields:

Field_01 - text field

Field_01_Array - global text field

Field_01_Count - global number

1.0 Get ridd off records with the same values,store them to the Field_01_Array and separate them with the sign you want.

SetField[ Table::Field_01_Array;

If (

PatternCount ( Table::Field_01_Array; Table::Field_01);

Table::Field_01_Array;

Table::Field_01_Array&"[separator can be whatever you want]"&Table::Field_01

)

]

2.0 Just count separators between records:

SetField[ Field_01_Count;

PatternCount ( Table::Field_01_Array; "[separator can be whatever you want]")

]

If there comes some extra empty space we can just Substitute or make count as:

PatternCount ( Table::Field_01_Array; "[separator can be whatever you want]") - 1

Dont forget that each appeared value in Field_01_Array is a single(unique).

This is very simple to applay and I did use for many, many calculations.

[Ender]

stash, I don't see how this would help get a count of Students by Department. The Student's aren't listed in a global, they're in individual records.

Perhaps you were thinking of counting them with a ValueListItems() function, like in this thread:

http://www.maclane.com/cgi-bin/ultimatebb.cgi?/ubb/get_topic/f/40/t/000297.html

[stash]

...global fields in my solution are added by need of calculation, just as a help...if you want to opserve something,count and make difference then you have to put all of that in one space(global field in this case)...that is the basic story of my idea...indeed I got it while opserving people and cars on the parking...how I can make difference between them? I can see them, I can count them but at the first, I see all of them at one parking....one space.