Your table structure looks reasonable and matches what you would likely find if you searched this forum for threads using "Survey" as your keyword in the searh. Not sure the purpose for Subjects here, but it doesn't look wrong.
You are correct that 4 different surveys would require exactly 4 records in the survey table. The typical data you would record in this table would be:
SurveyID: A serial number field that uniquely identifies each surver. This allows you to group your questions records by survey so that you can see which questions were asked on a given survey.
SurveyName: A text field that allows users to tell one survey from another since a survey ID will be to cryptic for that.
You can then add as many other fields as you need to document a given survey in its entirety. You might add a date field, for example, to record the date the survey was administered or a text field that summarizes the overall purpose of a given survey in several sentences. You could also add fields here that compute and/or store aggregate values that summarize data from all the responses to this survey.
The "Subjects" table is intended to store particular information about the test-takers (academic major, gpa, SAT and ACT scores, etc.). Would there be a better place to put that information?
As far as I can tell, this database will only be used to analyze, not administer, the surveys. Most, if not all, the data has already been collected and put into excel spreadsheets (much of the testing/grading had been done before I started this job).
I guess my problem is that I usually equate table and form (since they are usually very similar, if not the same). So, I was unsure what form/table I would go to to put in a Universtiy ID (User ID was a mis-type) to view their surveys taken, or where I would put my calculation fields. I'll just need to think about it more.
Univerisity ID could be a mistake here for your relationship to the Subject Table. You need an ID that uniquely identifies each test taker if you are going to use a Subject table to record Academic major, etc. for each person and if you need to examine the responses for a specific person taking your survey. You might find it useful to rename this table "Person" or "Respondant" and use it to record all data specific to one person who took the survey.
If you have multiple univeristies involved and you need to record data about each university, you'd add one more table for that data and link to it by a UniversityID.
One persistant problem I have been having is importing from Excel. I think this is because most databases are separated into different tables. This makes it so that when I want to import data, I can only import to one table at a time. Do I need to just upload my data piecewise, or is there a way for me to import the Excel data into the entire databse instead of a just a table? I already have to upload the data piecewise anyway (1 upload per question), so it adds up to a lot of extra time.
I hope that makes sense.
It's frequently necessary to upload from such a "flat file" structure repeatedly when importing into a relational system such as fileMaker in order to get the right data into the right tables. Uploading once for every question seems like major overkill and suggests that you need to consider structural changes to your system to avoid such a major timewaster in your import process.
One option you may want to consider is to set up a table in FileMaker that exactly matches the structure of your Excel file. You'd then import into Excel and use a script to loop through the data moving data from this "import only" table into the individual tables that make up your FileMaker system.
Alright, I finally got back to working on this database. I really like the looping idea, but I find scripts pretty confusing. I've searched for some "Loop Script" guides and I think I know how they work, but I'm not sure what commands I need to actually use. I'll continue looking for more examples and experimenting.
Let's use a simple example that you can then "scale up" to suit your actual file.
Let's say you have this data in the columns of your spreadsheet: Student Name; Value1, value2; Value3. And you want to populate two tables: Table 1: Students, with the student names and Table 2: Values with one related record for each of the three values.
Import the data into your temp table with the fieldnames: Student; Val1; Val2; Val3 so that the entire file is imported in one Import.
After Import, Run a script like this:
#This script should only be run from the Temp layout immediately after Importing records
Go To Record/Request/Page [first]
Set Variable [$Name ; Value: Temp::Student]
Go To Layout [Students]
Set Field [Students::Name ; $Name]
Set Variable [$StudentID ; Value: Students::StudentID ] //Students::StudentID should be defined as an auto-entered serial number field.
Go To Layout [Temp]
Go To Field [Temp::Val1]
Set Field [$Val ; Value: Get ( ActiveFieldContents ) ]
If [Not IsEmpty ( $Val ) ]
Go To Layout [Values]
Set Field [Values::StudentID ; $StudentID ]
Set Field [Values::Value ; $Val ]
Go To Layout [Temp]
Exit Loop If [ Get ( ActiveFieldName ) = "Val3"]
Go To Field [next]
Go To Record/Request/Page [next ; Exit after last ]
If you then use Manage | Database | Relationships to link Students and Values by StudentID, you can now place a portal to Values on the Students layout and you'll see the values from the three Value columns displayed in the portal.