Keys (Primary, Composite, Foreign), Relationships and Database Normalization

[JoeBruce]

Simple question: When you split the teachers and schools into two tables, how did you get the the get the "SchoolID_FK" field in "tblTeacherInfo" to have the correct school?



Everything above is getting to be information overload, and my actual questions seem to get lost in the chaos. I really do appreciate your help, Steve. I will be using your samples in my end product, but I'm still learning so much of the basics and trying to understand the how and why.

edit: Found the solution on YouTube.

I am curious what you meant by "the data is suspect." I am still pondering the issue of how to structure this to include total student numbers and all individual teachers for one program.

[ssanfu]

Simple question: When you split the teachers and schools into two tables, how did you get the the get the "SchoolID_FK" field in "tblTeacherInfo" to have the correct school?

Look at the "frmAddEditTeachers" form in design view. The combo box is bound to the field "SchoolID_FK". ($#!@, I meant to name the combo box "cboSchoolID_FK .... I missed that )

The combo box row source is referencing the Schools table. The field "tblSchools.SchoolID_PK" is the bound field.
In normal view, selecting a school from the combo box enters the "tblSchools.SchoolID_PK" value into the "tblTeacherInfo.SchoolID_FK" field.

[JoeBruce]

Interesting. All the work you did with forms and reports will be very useful for me. This allows the table to store the data as a number, but the form displays the text string associated with that number. In your opinion, is it better for the table to have it stored as a number vs. text?

Is there a good reference for developing strings like this? I can probably look at more of your examples to figure it out, but references for some of the "hidden" workings of Access would be great. Specifically, what is the significance of "AS Expr1"? I found a Microsoft article on expression syntax which is pretty good, but it doesn't make clear what "AS Expr1" means.

SELECT tblSchools.SchoolID_PK, [SchoolName] & "- " & [SchoolState] AS Expr1 FROM tblSchools ORDER BY [SchoolName] & "- " & [SchoolState];

[ssanfu]

Specifically, what is the significance of "AS Expr1"?

Basically, "Expr1" is an alias. Every object needs a name. When you create a calculated field in a query, if you don't name the "field", Access names that "field" using 'Expr" and a sequence number.
In this case, the calculation is concatenation of two table fields and some text. I didn't name the field, so Access did. The alias can be almost anything, but not existing field names, reserved words, etc.

(BTW, calculations in queries is GOOD, calculated fields in tables is BAD.)

Try this: change the name from "Expr1" to "Bananas".

SELECT tblSchools.SchoolID_PK, [SchoolName] & "- " &  [SchoolState] AS Bananas FROM tblSchools ORDER BY [SchoolName] & "- "  & [SchoolState];

Create a new query, switch to SQL view and paste in the above SQL. Execute the query. What is the field name?
Tables can also have aliases. Try this

SELECT S.SchoolID_PK, [SchoolName] & "- " &  [SchoolState] AS B FROM tblSchools S ORDER BY [SchoolName] & "- "  & [SchoolState];

Aliases are useful if you become excessively wordy. Say you named a table "tblElementary_Schools_in_Area_51".Your SQL would look like

SELECT tblElementary_Schools_in_Area_51.SchoolID_PK, tblElementary_Schools_in_Area_51.SchoolAddress, tblElementary_Schools_in_Area_51.SchoolState, [SchoolName] & "- " &  [SchoolState] AS Bananas FROM tblElementary_Schools_in_Area_51 ORDER BY [SchoolName] & "- "  & [SchoolState];

You could use the table alias to reduce the amount of typing

SELECT X.SchoolID_PK,S .SchoolAddress, X.SchoolState, [SchoolName] & "- "  &  [SchoolState] AS B FROM tblElementary_Schools_in_Area_51 X  ORDER BY [SchoolName] & "- "  & [SchoolState];

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

This allows the table to store the data as a number, but the form displays the text string associated with that number. In your opinion, is it better for the table to have it stored as a number vs. text?

This is part of the power of a RDBMS. If you normalize the table structure, you won't (shouldn't) duplicate data. (have the dame data n many tables)
Look at the teachers info table that has the teacher info AND the school info. Is the school name an attribute of a teacher?
This is called normalization. Teacher info (things about the teacher) goes in one table, school info goes into its own table.

You set up a relationship between the two tables, using a PK field and an FK field (1 to many). You only need one extra field (the FK field) in the teacher table instead of 6 fields for the school info. If you have 20 teachers working for a school and the school name changes from "Public School #10" to "Smithville School", you change the name once (in the school table) instead of having to change the name 20 times.... or maybe 50 times depending on how long the school has been open and how many teachers have worked there.

Some people use text fields as the PK/FK fields. I never do - see the link about Primary Key Tips and Techniques in post #4.
Also see the attached text file with links about normalization....

[JoeBruce]

This is more of a query question, but since it's a continuation of this discussion I'll keep it to this thread.

I am restructuring my most current database based on everything I've learned here and elsewhere. This has involved splitting data off into more tables, and doing some updating, duplication finding and deletion. Things have flowed pretty smoothly so far, but I could use some help in designing my final update query. I've color-coded the two tables and their fields to make things a little easier to follow. Blue is where I get the data to update, green is what needs updating.

I've made a query to locate duplicate data in [tblProgramsGiven] based on the fields [ProgramDate] and [NumberofStudents], and then list just the first PK (field [ProgramGivenID_PK]) for those duplicates. SQL below:

SELECT First(tblProgramsGiven.ProgramGivenID_PK) AS FirstOfProgramGivenID_PKFROM tblProgramsGiven
GROUP BY tblProgramsGiven.ProgramDate, tblProgramsGiven.NumberofStudents
HAVING (((Count(tblProgramsGiven.ProgramGivenID_PK))>1));

I want to update [tblTeacherAttendance].[ProgramAttendedID_FK] - right now, the [ProgramAttendedID_FK] field has all unique values that match [tblProgramsGiven].[ProgramGivenID_PK]. However, the [ProgramAttendedID_FK] field should not hold unique values; the SQL above returns the values which I need to update [tblTeacherAttendance].[ProgramAttendedID_FK], and I want to replace the [ProgramAttendedID_FK] field data which corresponds to the duplicate [ProgramGivenID_PK] with the results of above SQL (first PK of duplicate data). Example: [ProgramAttendedID_FK] data 16 & 17 are really the same program, so 17 should be updated to 16; data 71-74 are the same program, and all should be updated to 71.

My database is still very small - only ~120 records for tblTeachers and 86 records in [tblProgramsGiven] (and in [tblTeacherAttendance]), and there are only 8 records which have duplicates; I could do this manually, but I like the learning process. Thanks in advance!

I like sticking to Query Design view, but I am getting more comfortable entering Criteria using the "Build" tool and "Zoom" option for SQL.

Side note: does anyone else find the phrase "Update To:" in the Access query really confusing? It's what you're updating from! (I get it, you are updating the field to the value found listed in "Update to:")

[ssanfu]

I know it is highly unlikely, but should you include "ProgramType_FK" when checking for duplicate records? Is it possible to have 2 records with the same date and student count. but have different "ProgramType_FK" values? I wouldn't think you would want to change the [tblTeacherAttendance].[ProgramAttendedID_FK] values.

And I think I would use VBA to loop through and check for duplicate records, then change the [ProgramAttendedID_FK] values.