For John Vinson - Continued from Dee

[Guest]

Hi John,

Thanks for all of your help from the Advice from a Newbie message I posted
on the 10th.

I followed your instructions, but now am at the point where I need to create
the relationships. I understand the concept of one to one, one to many,
etc., but the primary key and non-primary key connection.

If you would be so kind, I would like to run the tables and their
relationships by you for your input.

FamilyInfo Table:
FamilyID
FamilyName
ParentName
Address1
Address2
City
Province
PostalCode
HomeNumber
WorkNumber
EMailAddress

Students Table:
StudentID
LastName
FirstName

CourseInfo Table:
CourseID
CourseName
MonthlyRate
SpecialRate

CourseDetailsPerStudent Table:
StudentID
CourseID
RegistrationDate
RegistrationFee
TuitionFee
DiscontinuedDate
VacationDate

DepositInfo Table:
DepositID
DepositNumber
DepositDate

PaymentInfo Table:
PaymentID
FamilyID
Mode
Date
Amount

Payment Allocation Table:
PaymentID
DepositID
CourseID
StudentID
Amount

The deposit is a bank deposit with the next bank deposit number, which is
related to all the cheques or cash deposited that week.

Relationships:

FamilyID in FamilyInfo table is one to many to Students table LastName

FamilyID in FamilyInfo table is one to many to FamilyID field in PaymentInfo
table

StudentID is one to one relationship with CourseDetailsPerStudent table, but
I think this should probably be one to many because one student may take
multiple courses, just not the same course, obviously at the same time

PaymentInfo table PaymentID field one to many to PaymentID in Payment
Allocation table

DepositID from DepositInformation is one to many to DepositID in
PaymentAllocation

StudentID from Students table is one to many to PaymentAllocation StudentID

CourseInfo table CourseID is one to many to both CourseID in
CourseDetailsPerStudent and also PaymentAllocation

Does this make any sense? I established the links in the relationships
window, dragging the primary key to the non-primary keys for the one to many.

Now, the questions are:

Are my relationships logical?
Now that they are established, do I create forms that allow viewing and
input of the data, then base queries and reports on the forms?

I feel like I'm getting close - queries and reports I'm OK at - it's the
basic setting up that really challenges me.

I would greatly appreciate your help - I was hoping to get this up and
running with a Switchboard by Jan 1.

THANKS so much!

 

[John Vinson]

Hi John,

Thanks for all of your help from the Advice from a Newbie message I posted
on the 10th.

I followed your instructions, but now am at the point where I need to create
the relationships. I understand the concept of one to one, one to many,
etc., but the primary key and non-primary key connection.

If you would be so kind, I would like to run the tables and their
relationships by you for your input.

Sure... sorry to be so long in responding! Answers inline:

FamilyInfo Table:
FamilyID
FamilyName
ParentName

Only one parent?

Address1
Address2
City
Province
PostalCode
HomeNumber
WorkNumber
EMailAddress

Students Table:
StudentID
LastName
FirstName

You'll need a FamilyID field as a foreign key to link the student to
their family.

CourseInfo Table:
CourseID
CourseName
MonthlyRate
SpecialRate

CourseDetailsPerStudent Table:
StudentID
CourseID
RegistrationDate
RegistrationFee
TuitionFee
DiscontinuedDate
VacationDate

looks good

DepositInfo Table:
DepositID
DepositNumber
DepositDate

PaymentInfo Table:
PaymentID
FamilyID
Mode
Date
Amount

Payment Allocation Table:
PaymentID
DepositID
CourseID
StudentID
Amount

The deposit is a bank deposit with the next bank deposit number, which is
related to all the cheques or cash deposited that week.

Relationships:

FamilyID in FamilyInfo table is one to many to Students table LastName

WRONG. Sorry, but a) children may have different last names than their
custodial parent's last name; names aren't unique (you could have
three families named Smith); and if FamilyID is an autonumber or
another numeric id, you cannot link it to a Text field. Linking fields
must be of the *same* datatype and size; an Autonumber is a special
type of Long Integer. You'll need a Long Integer FamilyID in the
Studenttable to link to the Autonumber (or Long Integer) FamilyID in
Families.

FamilyID in FamilyInfo table is one to many to FamilyID field in PaymentInfo
table

That's correct.

StudentID is one to one relationship with CourseDetailsPerStudent table, but
I think this should probably be one to many because one student may take
multiple courses, just not the same course, obviously at the same time

Right. One to many, not one to one. The Primary Key of
CourseDetailsPerStudent should consist of TWO fields - StudentID and
CourseID.

PaymentInfo table PaymentID field one to many to PaymentID in Payment
Allocation table

DepositID from DepositInformation is one to many to DepositID in
PaymentAllocation
StudentID from Students table is one to many to PaymentAllocation StudentID
yep


CourseInfo table CourseID is one to many to both CourseID in
CourseDetailsPerStudent and also PaymentAllocation

Does this make any sense? I established the links in the relationships
window, dragging the primary key to the non-primary keys for the one to many.

Now, the questions are:

Are my relationships logical?

With the exceptions noted above, yes.

Now that they are established, do I create forms that allow viewing and
input of the data, then base queries and reports on the forms?

Vice versa typically. Create Queries to select and sort the data, and
base your Forms on the queries.

I feel like I'm getting close - queries and reports I'm OK at - it's the
basic setting up that really challenges me.

I would greatly appreciate your help - I was hoping to get this up and
running with a Switchboard by Jan 1.

THANKS so much!

Best of luck and Merry Christmas!

John W. Vinson[MVP]

 

[Guest]

Hi John,

Thanks again for all of your help and advice. I followed your instructions
and things look great. I had trouble with one thing, though. You said that
the Primary Key of courseDetailsPerStudent should consist of two fields -
StudentID and CourseID. I couldn,t make this work. Did you mean that they
should be Foreign Keys from the Primary Key of Student ID in the Student
table?

I also need to add one more table that contains parents meetings
information: Date, who attended and notes. I guess I would relate that to
the FamilyID field in the FamilyInfo table as a one to many?

I also need to add more details to the student table, including DOB, Grade,
Age, Notes, and Yes/No fields for school reports handed in and psychologist
reports handed in.

Does this sound OK?

THANKS!!!!!

 

[Guest]

Hi Again!

This is turning in to quite the discussion. Thanks for your patience. In
the Student Table, I need to enter the age of the student. I have a DOB
field formatted as date and would like to add another field called Age that
has a formula to subtract the DOB from today's date and divide it by 365.25,
as I do in Excel. I've been working on various ways, but nothing seems to
work. Any suggestions?
Thanks!

 

[Guest]

Well, after lots looking around on the Web and here in the newsgroup, I guess
I now understand that I shouldn't put the age in the table, but rather in a
report, query or form.

Sorry about that!

 

[John Vinson]

Hi John,

Thanks again for all of your help and advice. I followed your instructions
and things look great. I had trouble with one thing, though. You said that
the Primary Key of courseDetailsPerStudent should consist of two fields -
StudentID and CourseID. I couldn,t make this work. Did you mean that they
should be Foreign Keys from the Primary Key of Student ID in the Student
table?

They should be BOTH: each field individually is a Foreign Key to that
respective table; and you should ctrl-mouseclick both fields in table
design view and click the Key icon so that they form a joint,
two-field primary key. This will prevent a student from being enrolled
in the same course twice, and allow your queries to be updateable
(since you MUST have a Primary Key for multitable queries to allow
updating).

I also need to add one more table that contains parents meetings
information: Date, who attended and notes. I guess I would relate that to
the FamilyID field in the FamilyInfo table as a one to many?
Yes.

I also need to add more details to the student table, including DOB, Grade,
Age, Notes, and Yes/No fields for school reports handed in and psychologist
reports handed in.

The reports might be yes/no fields, or they might be another many to
many relationship: a table of Reports (listing the type of reports
expected) and a table with StudentID and ReportID, and (say) date
handed in, summary, etc.

As you found elsethread you should store DOB but not Age. The best
expression for calculating age is

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd"), 1, 0)


John W. Vinson[MVP]

 

[Guest]

Sorry for this additional post, but is this correct:

I added DOB, School Grade and name of school in the student table.

I added Mother first name, Mother last name, same for father, plus their
contact tel numbers to the FamilyInfo table.

I added a new table called StudentNotesandReports that contains:
StudentID (foreign key from StudentID primary key in Student table)
Date
Notes
PschologistReportHandedIn Y/N field
SchoolReportHandedIn Y/N field

I added another new table called ParentMeetings that contains:
FamilyID (foreign key from FamilyID primary in FamilyInfo table)
StudentID (foreign key from StudentID primary in Student table)
DateofMeeting
ParentAttendee1
ParentAttendee2
Notes

Does this sound like I'm on the right track?

thanks!

 

[Guest]

I'm so sorry for being quite the pain, but I'm really excited about learning
this stuff.

I'm unclear about the two field primary key. I selected the StudentID and
CourseID fields in CourseDetailsPerStudent and clicked the primary key
button, which was fine. When I try to save, though, it gives the error
message:

Index or primary key cannot contain null value.

I've looked this up, but can't seem to figure it out.

THANK YOU, THANK YOU, THANK YOU!

Hi John,

Thanks again for all of your help and advice. I followed your instructions
and things look great. I had trouble with one thing, though. You said that
the Primary Key of courseDetailsPerStudent should consist of two fields -
StudentID and CourseID. I couldn,t make this work. Did you mean that they
should be Foreign Keys from the Primary Key of Student ID in the Student
table?

They should be BOTH: each field individually is a Foreign Key to that
respective table; and you should ctrl-mouseclick both fields in table
design view and click the Key icon so that they form a joint,
two-field primary key. This will prevent a student from being enrolled
in the same course twice, and allow your queries to be updateable
(since you MUST have a Primary Key for multitable queries to allow
updating).

I also need to add one more table that contains parents meetings
information: Date, who attended and notes. I guess I would relate that to
the FamilyID field in the FamilyInfo table as a one to many?
Yes.

I also need to add more details to the student table, including DOB, Grade,
Age, Notes, and Yes/No fields for school reports handed in and psychologist
reports handed in.

The reports might be yes/no fields, or they might be another many to
many relationship: a table of Reports (listing the type of reports
expected) and a table with StudentID and ReportID, and (say) date
handed in, summary, etc.

As you found elsethread you should store DOB but not Age. The best
expression for calculating age is

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd"), 1, 0)


John W. Vinson[MVP]

 

[John Vinson]

I'm so sorry for being quite the pain, but I'm really excited about learning
this stuff.

I'm unclear about the two field primary key. I selected the StudentID and
CourseID fields in CourseDetailsPerStudent and clicked the primary key
button, which was fine. When I try to save, though, it gives the error
message:

Index or primary key cannot contain null value.

I've looked this up, but can't seem to figure it out.

Eh? The message is clear enough: if either StudentID or CourseID is
NULL in any record in the table, you're not allowed to use that
combination of fields as a primary key. This is reasonable - you
wouldn't want an enrollment record which enrolls a student in no class
at all, nor one which has a course but no student enrolled!

Run a Query on your table searching for IS NULL in these two fields.
Any record with one of the fields null is probably in error and should
simply be deleted. Once you've done so you'll be able to create the
Primary Key.

John W. Vinson[MVP]

 

[John Vinson]

Sorry for this additional post, but is this correct:

I added DOB, School Grade and name of school in the student table.

I added Mother first name, Mother last name, same for father, plus their
contact tel numbers to the FamilyInfo table.

Well... maybe. In the 21st century there are quite a few "blended"
families. The parents might be separated, there might be stepparents
on both sides, etc. It may be overkill for your app but you may want
to consider a Parents or FamilyMembers table.

I added a new table called StudentNotesandReports that contains:
StudentID (foreign key from StudentID primary key in Student table)
Date
Notes
PschologistReportHandedIn Y/N field
SchoolReportHandedIn Y/N field

I would NOT recommend this approach. Storing data in fieldnames is
*never* a good idea. If you're going to normalize this, I'd suggest
having a table of ReportTypes (with fields ReportTypeID and
ReportType, e.g. "Psychologist Report", "School Report", "Parental
Permission Slip", ... - any type of report which might be handed in).
Your StudentNotesAndReports table would then have fields StudentID,
ReportTypeID, ReportDate (don't use Date as a fieldname, it's a
reserved word) and Notes.

I added another new table called ParentMeetings that contains:
FamilyID (foreign key from FamilyID primary in FamilyInfo table)
StudentID (foreign key from StudentID primary in Student table)
DateofMeeting
ParentAttendee1
ParentAttendee2
Notes

Again, it may be overkill but it's probably preferable to NOT have
repeating fields like this. You might have (again) a blended family,
with the birth father and birth mother *and* the stepfather all in
attendance.

Does this sound like I'm on the right track?

You're clearly getting there!

John W. Vinson[MVP]

 

[J SCHWARTZ]

John,

I am majorly impressed with your expertise and helpful attitude in this
newsgroup. What a pleasure to follow your design "class" with Dee, who
could just as easily be me.

jo
(former R:Base database consultant/programmer, current Access
"know-only-enough to be extremely dangerous" tinkerer)

 

[Guest]

Hi,

I couldn't agree with you more about John's expertise, helpful attitude and
PATIENCE with a newbie. He's incredibly helpful.

Dee

 

[Guest]

OK, I've made the changes for the reports, creating a new table called
ReportTypes with ReportTypeID (primary key) and ReportType. I modified the
StudentNotesAndReports to include StudentID (foreign key from PK in
StudentTable), ReportType (linked from ReportType in ReportTypes table, but
I'm not sure about primary and foreign keys for these tables) and ReportDate.

Does this make sense? I'm hoping that the tables set-up is the most
important part of this in terms of everything else working properly if they
are set up correctly. Of course, now I have to learn all about normalizing
to see why my initial approach wouldn't work. So much to learn... I'm in
awe of you!

Re the null field, I guess I'm just exhausted from work, holiday stuff,
house and family and didn't even realize I had a blank record in the table.
Sorry for my stupidity. I am blonde, by the way

Back to work on this! Thanks again -- you're incredibly patient and helpful.

If I don't have a chance to test this all out before Christmas, happy
holiday and warm wishes for a peaceful and safe holiday season and a
wonderful new year.

Dee

 

[John Vinson]

John,

I am majorly impressed with your expertise and helpful attitude in this
newsgroup. What a pleasure to follow your design "class" with Dee, who
could just as easily be me.

jo
(former R:Base database consultant/programmer, current Access
"know-only-enough to be extremely dangerous" tinkerer)

<blush> Thank you! As you may be able to tell, I get a kick out of
seeing the lightbulbs light up as people new to the arcane world of
relational design "get it".


John W. Vinson[MVP]

 

[John Vinson]

OK, I've made the changes for the reports, creating a new table called
ReportTypes with ReportTypeID (primary key) and ReportType. I modified the
StudentNotesAndReports to include StudentID (foreign key from PK in
StudentTable), ReportType (linked from ReportType in ReportTypes table, but
I'm not sure about primary and foreign keys for these tables) and ReportDate.

The StudetnNotesAndReports table should contain a ReportTypeID as a
foreign key - *not* a ReportType. On the other hand, if there aren't
very many reports, the ReportTypes table could contain just *one*
field - the ReportType itself, text - as the primary key; this would
waste some space in your StudentNotesAndReports table but would save
time in that it wouldn't have to look it up.

Does this make sense? I'm hoping that the tables set-up is the most
important part of this in terms of everything else working properly if they
are set up correctly. Of course, now I have to learn all about normalizing
to see why my initial approach wouldn't work. So much to learn... I'm in
awe of you!

You're absolutely right - getting the table design right is like
getting the foundation laid, square and true, for a new house. The
framing and trim are MUCH easier given that... and can be impossible
if the foundation isn't right!

Re the null field, I guess I'm just exhausted from work, holiday stuff,
house and family and didn't even realize I had a blank record in the table.

VERY easy to do.

Sorry for my stupidity. I am blonde, by the way

So am I (or, well, I used to be - thinning and grey is more accurate
now)!

Back to work on this! Thanks again -- you're incredibly patient and helpful.

If I don't have a chance to test this all out before Christmas, happy
holiday and warm wishes for a peaceful and safe holiday season and a
wonderful new year.

A joyous Christmas and a happy productive New Year to you as well!

John W. Vinson[MVP]

 

[J SCHWARTZ]

Relational I get, it's Access I don't<g>. R:BASE (it eventually came out
with Window's versions, but I only was good with the widely used Dos version
circa 1987) made so many things easy for the developer and the techniques
weren't as hidden (to me) as the comparable techniques in Access. The RBASE
programming language was pseudo SQL and more readable than VBA so it was
easier for developers to step in and take over abandoned or poorly
constructed RBASE applications and understand what was going on.

IMHO to do anything the least bit user friendly, idiot proof, and menu
driven, in either system, one has to learn at least some programming,and
usually quite a bit. I realize I'm comparing apples to oranges to some
degree in that designing for any Windows relational dbms is bound to require
a different approach than a text based DOS system. I'd love to rewrite some
of my old apps in both the Windows version of Rbase or Access, but health
restrictions limit how much time I can spend using a keyboard, and I really
believe the only way to learn a new system is to plunge in and write simple
applications, making them increasingly more polished as you learn new
techniques. I've only created my address book in Access, and you can
imagine how basic that is, and how spiffy I could make it if I really knew
what I was doing and had the time. At my pace, I'm never going to enhance
anyone's Access system (except possibly my own) beyond assigning a default
value to a field entered on a form or something equivalently benign. One
has to know one's limitations and "do no harm"<g>.

As for the delight of seeing the lightbulb go off, in my volunteer capacity,
setting a couple of defaults and cloning the database for a new year (don't
ask) was enough to make them think I was a genius. You wouldn't believe ---
or maybe you would --- how little knowledge exists at the administrative
level of many organizations. Noone trains them so they make their
applications produce what they must in any brute force manner they can
figure out. It's a miracle anything gets done. The redundancy would turn
your stomach.

You remind me so much of a very kind and generous guy named Ray on a Delphi
Forum, The Hammer Mill. Ray has the patience of Job with both people and
problems. He can troubleshoot Windows problems completely via the forum,
and untangle the cryptic descriptions of these problems (i.e. "Windows
stopped working") with such skill, it's like watching a work of art unfold.
One learns a lot following threads like his and yours and I can't thank
people like you enough for the manner in which you teach us.

jo

 

[John Vinson]

You wouldn't believe ---
or maybe you would --- how little knowledge exists at the administrative
level of many organizations.

<SNORK!>

Yes. I would. <g>

The phenomenon that gets me chuckling (wryly) is when an administrator
is jaw-dropping amazed at something that took me forty-five seconds to
do... and then casually asks "Can you have it do <fitb>?" which would
require a 60-hour total redesign.


John W. Vinson[MVP]

 

[J SCHWARTZ]

Yes, I've had that too. But, as I said, I know my limits. It's still good
for the ego, tho, and when you've had to take yourself out of the career you
adored for 20 years, that little "hey, you know your stuff" (whether they're
right or wrong) is wonderful medicine.

"SNORK" is a great word.... or does it stand for something?.

jo

 

[John Vinson]

"SNORK" is a great word.... or does it stand for something?.

Purely onomatopoetic, I assure you!


John W. Vinson[MVP]

 

[Lynn Trapp]

Purely onomatopoetic

You do and you'll clean it up... <g>