importing when cases are not unique

[charlesoutcalt]

I'm stumped by this import challenge:

I have set up a relational database for students at my university. Things are working well so far--I have several different tables, all keyed by student ID, containing fields such as GPA, credits by term, etc.

The last step in creating this database is importing degree information. Here's the problem:

My text file containing information on degrees lists each degree as a separate record, keyed by student ID. If students received just one degree, this would be no problem. However, many students receive more than one degree, and I need to track all of them. The maximum number of degrees for any student is five.

I would like to create a table with records like this:

ID

degree one type

degree one major

degree one date

degree two type

degree two major

degree two date

degree three type

degree three major

degree three date

degree four type

degree four major

degree four date

degree five type

degree five major

degree five date

How can I do this? I've been able to import the degree file, and link it via ID to my other tables, but the degree file contains multiple records for those students who have earned more than one degree.

Any assistance you can give would be very much appreciated!

[LaRetta]

I recommend that you instead make each degree a record. Importing will be a snap as well. Under your proposed structure, your reporting will be limited also.

[charlesoutcalt]

You are being so helpful! I appreciate it.

I have imported this file, with each degree as a record (in fact, this is the file we've been discussing in the other forum, on dates; the date in question is the degree date).

I don't know how to work with this imported file, though. For example, I need to be able to determine if and when students received a degree. If I have one file listing all students IDs, with a field indicating their membership in a particular entering class, I need to look to see if they have received a degree within a given date window--say, between May 2004 and May 2005.

The part where I get stumped is that I don't know how to look at each and every one of those imported degree records for a student who has earned more than one degree.

If my first table has just one record for each student, but then my degree table has multiple records for some students (those who have more than one degree), how can I check EACH of those degree records to see whether a student ever earned a particular kind of degree?

[LaRetta]

You need to include a field which identifies the Degree in your 'Match Records Based Upon'. So you may the = on both StudentID AND Degree. Then select 'Update matching records in found set' and 'Add remaining records' ... that is, if I understand you correctly. Each smallest entity should be a record.

how can I check EACH of those degree records to see whether a student ever earned a particular kind of degree?

By this, do you mean that you don't want to import duplicate degree/student records? If so, then the above would hold. But I still feel a bit unclear on the result you require (record-wise).

LaRetta

Edited December 7, 2006 by Guest
Added a bunch

[charlesoutcalt]

First, thanks so much for taking the time to try to pound this into my head

Here's what I need to do: Determine whether a student received a particular kind of degree (BA, BS, AS, Ph.D., etc.), and when.

If I import that degree file, then I will have multiple records for some students. For example, student Joe Blow might have three records, one showing his AS degree, another showing his BA, and a third showing his Ph.D.

How can I do something simple, but vital, such as answering the question: How many women (or students named Joe) earned a BA?

How do I get over that hump of having one record per STUDENT in one table, but then one record per DEGREE in the second table?

[LaRetta]

Things are working well so far--I have several different tables, all keyed by student ID, containing fields such as GPA, credits by term, etc.

How do I get over that hump of having one record per STUDENT in one table, but then one record per DEGREE in the second table?

You import those records into a Degree table. Then you join the tables on the StudentID using =. There are then many ways of viewing/accumulating that data; portals, summary reports, count calculations etc. I get the feeling that we are just *missing* each other a bit. Simply, before you can do anything with the information contained in those imports, you need them in FileMaker as records. Truly, your only option there is to determine whether to import them ALL into your Degrees table or only those with unique characteristics. Pull them all in and summarize them per student, yes?

Talk to me some more and maybe others can jump in as well. [color:red]Please tell us how you are structured; because it seemed strange that you have (what appears to be) a 1:1 (one-to-one) relationship to your Students table (GPA?). Are we narrowing down your needs?

LaRetta

Edited December 7, 2006 by Guest
Added red

[charlesoutcalt]

Oh yes, this is definitely helping. . .

I'm familiar with importing the records, and then joining them to my 'main' table by relating the records via the relationship on the ID field.

Here's where my competence ends (abruptly!)

If I have IDs linked to multiple records (say, Joe Blow's record is linked to his three records in the degree table), how can I determine whether any of Joe Blow's degrees are of type "BA"? I would love to be able to tell how many students in that main table earned BAs, how many earned BSs, how many earned Ph.D.s (and even how many earned BSs and Ph.Ds).

Does this help?

[LaRetta]

Yep, I believe it's helping. I was just thrown because I don't see this as an import issue, if I understand correctly.

Does each record in your Degrees table have the following fields with data?

ID

Degree Type

Degree Major

Degree Date

Instead, we may be looking at portals, aggregate calculations or summaries.

Or am I still missing your need here. What determins whether someone is a PhD? Where does that data exist?

[charlesoutcalt]

Oh, my mistake. I was hitting this problem at the stage of import, but I guess it's more a design issue. . .

Yes, my degree records contain the info you mentioned. That 'degree type' is the field that tells me what kind of degree was awarded: BA, BS, Ph.D., etc.

[LaRetta]

I would love to be able to tell how many students in that main table earned BAs, how many earned BSs, how many earned Ph.D.s (and even how many earned BSs and Ph.Ds).

So how do you want to view/know this information? Do you want a printed report, showing the counts per Degrees per student? Do you want to view a Student and see a portal with their Degrees and - outside the portal - it lists the count of each?

Once we know how you want these totals displayed, the rest should be easy.

LaRetta

[charlesoutcalt]

This is just great, so helpful.

The most important thing for me to be able to show will be graduation rates. In the 'main' table, I have a field that indicates membership in a particular entering class (class of 1998, 1999, etc.). I need to be able to state how many students with a value of '1' for that field earned a BA or a BS within a given date range. So, for example, I would need to know how many of the 500 members of the class of 1999 earned a BA or BS between May 2003 and May 2005. I guess this means I would need to test a couple conditions at the same time: What was the degree, and when was it earned? How can I test those conditions across multiple degree records in that second file?

In addition, I would love to be able to show a list of students, with their degrees. This would be a nice feature, but it's not nearly as important as showing graduation rates. I think I know how to do this--use a portal. . .

I am very excited about this. I was afraid I would need to slog through many days of ugly SPSS programming in ugly SPSS, but maybe FM will let me leave that program untouched. . .

THANK YOU!

[charlesoutcalt]

PS: I must put the computer, and then myself, to sleep now, but I am so grateful to you for this help, and I can't wait to get back to this early tomorrow am. Any advice you have now would surely be appreciated, and I am eager to try to put it into practice asap.

[charlesoutcalt]

Just checking to see if you had any further advice on this issue.

Unfortunately, I am still stumped. . .

[LaRetta]

Give this a try (attached). I used a Main Table to show that it doesn't matter where you attach this functionality. You could also just create a sub-summary report in your Degrees table which, aftering finding your date-range and Major, could then produce totals using summary fields.

LaRetta

School.zip

[charlesoutcalt]

This works perfectly. So simple, once you show me how it's done.

I really appreciate this. I owe you one (or a dozen!).

[charlesoutcalt]

Hello! Once again, I turn to this community for a little help. . .

Thanks to previous good suggestions, I have a great means of displaying records in table 1 that match multiple criteria in table 2. This relationship is of the "field a must = field a1," and "field b must = field b1" type. So far, so good, and this is all working like a charm.

However, I'd like to add an additional matching requirement, so that records in table 1 must match not only those in table 2, but also those in table 3. I have created a relationship between the fields in table 1 and table 3, and specified that they must match, but this requirement does not have any effect on the portal I have established in table 1, which shows records from table 2.

How can I get that portal in table 1 to show only records with fields that match in table 2 AND table 3?

Do I need to repeat the field on which I want to match from table 3 on table 2? That seems unlikely, since all I have learned about FM is that this sort of clumsy data repetition is unnecessary.

There must be a way for a portal to reflect only records that match values on multiple tables.

Thank you very much!

[charlesoutcalt]

Hey LaRetta! All is working great, but one quick (I hope) question:

How can I extend the matching requirement to data in another, related, table? I would like to be able to display in that portal on the 'Main' table only records from students who entered in any given year.

That record exists in my Students table. When I create a matching relationship to that table, though, there is no effect on the records displayed in the portal. Should I re-create that field on the Student Degrees table?

[LaRetta]

I recall you mentioning a 1. Is that the criteria?

It may be as simple as relating your Main directly to Students FIRST on the 1 (using =). Then Students to Student Degrees (on global filters in Students). You can still place those Student globals on your Main and fill them in. When you then display THAT Student Degrees portal on Main, it will be filtered to only those in Students with a 1. A sample file (of what you have so far) would help us a great deal.

We need to know how that additional criteria relates in your file. You can't relate to a third table because the key (possibly that 1?) must be in your Degrees table (indexed) and it can't be if it's from a related table (which makes it unstored). You'll have to find another way around the issue.

LaRetta

Edited December 10, 2006 by Guest

[charlesoutcalt]

LaRetta, I can't tell you how much I appreciate all this help. Thanks for bearing with me.

I wish I could post a sample, but the actual files are a lot more involved than I have been describing them, with tons of extraneous fields that would only cloud the issues. I'll describe my current question, though, with a thousand thanks for your help to date.

After you posted that helpful sample, I used it to set up my tables, and it works fine. Now, however, I need to be able to select only students from a particular freshman year. I have a field in my Student file that indicates this year. The values are 1999, 2000, 2001, etc.

Currently, on the Main table, I am able to use the checkboxes to show just records indicating receipt of a particular degree. What I'd like to be able to do is show records indicating that degree, but for members of a particular freshman year. That field is now stored on the Student file.

How can I restrict data display on the Main table to degrees of a particular type (data stored in the Degrees table) AND students of a particular year (data stored in the Student table)? There are plenty of other ways I'd like to restrict data in the future--say, students of a particular year, with SAT scores above a particular number--but I think that, if we can figure out this particular puzzle, I will be able to extend the method to other fields in the Student table.

Edited December 10, 2006 by Guest

[LaRetta]

I don't know.

All I would know to do is to write that data to your StudentDegrees table to use in the join. Once all the records are written, it can pre-fill upon Degree creation (or use a Lookup). Or switch your join from Main - StudentDegrees to Main - Students and put your globals in Students (and relate those to StudentDegrees). You can then display the StudentDegrees directly on your Main layout.

There are Relationship Masters here on Forums; I'm not one. Maybe they can make suggestions.

LaRetta

[charlesoutcalt]

Hmm. I've been tossing this back and forth in my mind. I think your second idea would work, but I am reluctant to introduce duplicate instances of student records in that Students table.

In this project, there are several dozen tables, each of which is related to the other in a one-to-one relationship via the student ID field. (The other tables contain date-specific information, such as number of credits enrolled during a particular term, or GPA as of a particular date. I had to create so many tables because the data came to me in many separate csv files.)

Since I've worked so hard to maintain this one-to-one relationship throughout the project, I am hesitant to change things now by importing those degree records into the Students table. I wonder if there is another way to go about this?

If anyone is new to this forum and wants to offer an idea, here's the short version of what I want to do:

I have a table called Students that lists all students at my university, along with demographic data, such as home town, first term, etc. There is one record per student in this table.

Second, I have a table called Degrees that lists all degrees received by students. There is one record per degree in this table. Some students have earned more than one degree, and so I can't relate this table to the Students table in a one-to-one way.

Third, I have a table called Main that provides a place to list all students who received a degree of a particular type within a particular date range. The degree type and date range fields are in the Degree table. I am using global values in this table for date and degree type, and creating a multiple match criteria with the Students table to pull student name.

I have to say that all of the above is thanks to LaRetta's patient and expert help!

Now, though, I need to do something more: I need to be able to match to degree types and date ranges in the Degree table AND to a field (freshman year) in the Student table. In other words, I need to be able to display, for example, all records showing the receipt of a BS degree in 2003 (these fields from the Degree table) for students who began in fall 1999 (this field from the Student table). In addition, in the future I will need to match to more fields on the Student table (say, by selecting students with an SAT score above 600, or a GPA below 3.0).

So, as I understand it, this question boils down to my need to display only records that match criteria in not one but two tables.

Any ideas/suggestions? I would really appreciate them!

[comment]

I think this task may be better handled by a Find, instead of relationships. In your example, you would start by finding - in the Degrees table - the degrees that match the degree criteria. Then go to related records (match all records in the found set) in the Students table, and constrain the found set by the student criteria.

If you absolutely must have the results in a portal, you will have to move some of the global fields to the intermediate table. For example, if your TOG goes:

Viewer - Degrees - Students

then the global fields filtering the students must be in the Degrees table, and be a part of the Degrees - Students relationship.

You can still place those globals on a layout of Viewer, so from user's point-of-view this is transparent.

[charlesoutcalt]

Thanks. Let me see if I can say this back to you, just to be sure I'm understanding your suggestion:

1. In my Degree table, I would find the degree records I'm interested in (for example, BA degrees awarded in 2003).

2. Next, I would go to related records in the Student file. [color:purple]I'm not sure how to do this, though. I've searched FM help for "go to related record," but there's nothing there that helps.

3. Once I get to the related records in the Student file, I can constrain my search by all the criteria that I need.

If this is correct, can you tell me how to "go to related records"?

Also, I'm not familiar with the acronym TOG, and, for the life of me, I can't figure it out.

Thanks!

[comment]

Yes, that is correct. This is not the only way to do it - you CAN search on related fields, too - but it is more efficient this way because all searches are performed on indexable fields.

Go to Related Record[] is a script step. It is one of the few things you cannot do manually.

TO is short for 'Table Occurrence'. TOG is a group of TO's joined together by relationships.

[charlesoutcalt]

Ah, I understand now.

I will check this out. Seems like this idea of going to related records might be just the ticket.

I don't know what I would do without these forums. Better than any book I have found, and WAY better than beating my head against the FM 'help' function. . . :)