For John Vinson

[Guest]

Hi John,

I'm not sure if I should have added this post to our last message because I
wasn't sure you would be notified.

I did split the family table into family and PhonesAndEmails. I added the
fields you suggested, plus the e-mail address field and a field called
ContactRelationship, which should contain whether the person is a mother,
father, etc.

You had asked a questions about the courses table. If a course is given
repeatedly over time, or if there are three teacher all teaching English at
the ame time, will each section have its own record in CourseInfo? Or might
I need a one to many from Courses to Sections? I don't really understand
this.. Would you mind clarifying for this blonde?

Parent meetings I didn't change, because it would strictly be a family
meeting. Only parents (or guardian), plus up to 4 of their children. The
notes would be general ones regarding the meeting. Do you still think I
should split this up?

Another question would be if I want to keep a table that contains families
that are no longer actively taking courses, how would I do this. I would
like this for historical purposes, plus if a family re-enrols, I wouldn't
have to re-record everything.

Thanks!!!!!

Dee

 

[Guest]

Another question!

I understand that when creating forms, you had said to create a query first,
then the form, so that I can filter and sort.

I assume this is also true for reports.

However, if I want a very simple report that doesn't require filtering, I
assume I just base the report on the table and it would be unnecessary to
create a query to base it upon? I can choose by which fields I wish to sort
when creating the report.

Thanks AGAIN!

 

[John Vinson]

However, if I want a very simple report that doesn't require filtering, I
assume I just base the report on the table and it would be unnecessary to
create a query to base it upon? I can choose by which fields I wish to sort
when creating the report.

Thanks AGAIN!

Quite correct.

In practice, I find that *usually* a Report will require information
from some lookup tables (yes, I hate lookup *fields* but I use lookup
*tables* routinely!), but if all the information that you need on the
paper is in the same table, by all means use it directly. (Access will
create a hidden query in the background anyway so you're still
actually using a Query but you don't need to mess with it!)

John W. Vinson[MVP]

 

[Guest]

Thank you for your response. I print everything you write out and refer back
to it often!

I have been struggling all day with the creation of queries, forms and
reports.

For example, if I want a form in which I can enter/view family information,
student information and contact (phone numbers, etc.) information... is this
possible?

I tried to create queries for these things, but it doesn't seem to work.
Then, when I create the form, it doesn't ask if I want a sub-form. I'm using
the wizard.

I then re-checked my relationships and enforced referential integrity where
I thought I should, but again, I'm a bit lost.

Here is what the tables look like at this point, with their pks, fks, etc.
You're probably sick to death of this, but you have no idea how grateful I am
for your patience, expertise and advice.

I'm supposed to have this finished by Friday and my nerves are starting to
fray.

I need reports such as:

Deposit slips that list cash and cheques per course code

Student labels with just their names (that should be easy)

Family labels with name and address

Student List

Family List

List of students per subject

Total of students per subject

Overdue payment report

And so on... I think if I can figure out just how to put together one
query, form, report on one of these, I will be able to do the others...

Thanks so very much!

* * denotes primary key; * denotes foreign key

Table : FamilyInfo
**FamilyID
Address1
Address2
City
Province
PostalCode


Table: PhonesAndEmails
**PhoneID
*FamilyID (one-to-many with referential integrity enforced)
ContactLastName
ContactFirstName
RelationshipToStudent
PhoneNo
PhoneType
Emergency (yes-no)
E-mailAddress

Table: Students
**StudentID
*FamilyID (one-to-many with referential integrity enforced)
StudentLastName
StudentFirstName
DOB
SchoolGrade
NameOfSchool


Table: ParentMeetings
**MeetingID
*FamilyID (one-to-many with referential integrity enforced)
*StudentID (one-to-many with referential integrity enforced)
DateOfMeeting
ParentAttendee1
ParentAttendee2
Student1Present
Student2Present
Student3Present
Student4Present
Notes

I’m not sure if this makes sense. Each ParentMeeting will be with only one
family with up to 4 of their children attending, so need names of students in
addition to names of parents.


Table: ReportTypes
**ReportTypeID
ReportType

Table: StudentNotesAndReports
**NoteReportID
*StudentID (one-to-many with referential integrity enforced)
*ReportTypeID (one-to-many with referential integrity enforced)
ReportDate
Notes

Table: CourseInfo
**CourseID
CourseName
MonthlyRate
SpecialRate

Table: CourseDetailsPerStudent
**StudentID
**CourseID
(The StudentID and CourseID are combined pks, with but I don’t know if this
is right and am unsure of the relationship between these fields and the
Student and Course tables)
RegistrationDate
RegistrationFee
TuitionFee
DiscontinuedDate
VacationDate


Table: Awards
**AwardID
AwardName
AwardDescription

Table: StudentAwards
StudentID (one-to-many with referential integrity enforced)
AwardID (one-to-many with referential integrity enforced)
AwardDate
Comments

Table: PaymentInfo
**PaymentID
*FamilyID (one-to-many with referential integrity enforced)
Mode
Date
Amount
ChequeNo


Table: PaymentAllocation
*PaymentID (one-to-many with referential integrity enforced)
*DepositID (not sure what relationship should be or if ref. int. should be
enforced)
*CourseID (not sure what relationship should be or if ref. int. should be
enforced)
*StudentID (not sure what relationship should be or if ref. int. should be
enforced)
Amount


Table: DepositInfo
**DepositID
DepositNumber
DepositDate

 

[John Vinson]

Thank you for your response. I print everything you write out and refer back
to it often!

I have been struggling all day with the creation of queries, forms and
reports.

For example, if I want a form in which I can enter/view family information,
student information and contact (phone numbers, etc.) information... is this
possible?

Possible and perfectly routine.

The trick is - it's not just "a form" based on a complicated Query;
it's a Form with Subforms, each based on a table or a simple,
one-table query! THat is, you'ld have a Form for Families, with a
subform for Students, and probably a second subform for Phones, and so
on.

I tried to create queries for these things, but it doesn't seem to work.
Then, when I create the form, it doesn't ask if I want a sub-form. I'm using
the wizard.

Well... you're getting into some forms that may be more sophisticated
than the wizard knows about. You can use the Subform tool on the
toolbox to add a new subform (rather than trying to get the wizard to
create this entire multi-table form in one go), or you can create a
(say) Phones subform, and simply drag it off the Forms window onto
your mainform.

I then re-checked my relationships and enforced referential integrity where
I thought I should, but again, I'm a bit lost.

RI has a rather distant relationship to forms design... but it's only
distant. You do need to get your RI correct (for data integrity), and
having RI set will (for example) automatically bring in the right
fields for Master/Child Link Fields, but RI by itself won't build a
form for you!

Here is what the tables look like at this point, with their pks, fks, etc.
You're probably sick to death of this, but you have no idea how grateful I am
for your patience, expertise and advice.

Ok, let's check it out:

I'm supposed to have this finished by Friday and my nerves are starting to
fray.

I need reports such as:

Deposit slips that list cash and cheques per course code

Not hard, create a Query on all the relevant tables and use the
Report's Sorting and Grouping to group by Deposit Slip and then by
Course Code. Use the section headers and footers to display totals per
course and grand totals per deposit slip.

Student labels with just their names (that should be easy)

Family labels with name and address

Student List

Family List

List of students per subject

Total of students per subject

Overdue payment report

And so on... I think if I can figure out just how to put together one
query, form, report on one of these, I will be able to do the others...

See above. I used to do all sorts of complicated Reports and
Subreports but now I find that the Sorting and Grouping is VERY
capable of most of these kinds of things.

Thanks so very much!

* * denotes primary key; * denotes foreign key

Table : FamilyInfo
**FamilyID
Address1
Address2
City
Province
PostalCode

Is this Canadian province or what? You might want a small table of
Provinces (with the text code and the province name) just for ease of
creating a combo box on your form.

Table: PhonesAndEmails
**PhoneID
*FamilyID (one-to-many with referential integrity enforced)
ContactLastName
ContactFirstName
RelationshipToStudent
PhoneNo
PhoneType
Emergency (yes-no)
E-mailAddress

ok... these might be contacts who do not live with the student? Will
you need to know THEIR addresses?

Table: Students
**StudentID
*FamilyID (one-to-many with referential integrity enforced)
StudentLastName
StudentFirstName
DOB
SchoolGrade
NameOfSchool
ok


Table: ParentMeetings
**MeetingID
*FamilyID (one-to-many with referential integrity enforced)
*StudentID (one-to-many with referential integrity enforced)
DateOfMeeting
ParentAttendee1
ParentAttendee2
Student1Present
Student2Present
Student3Present
Student4Present
Notes

I’m not sure if this makes sense. Each ParentMeeting will be with only one
family with up to 4 of their children attending, so need names of students in
addition to names of parents.

well... this is non-normalized and will be harder than it needs to be
to search. If you want to find out all the meetings that Janie Smith
attended, you'll need a more difficult search of four fields. Take
another look at my prior suggestion of a one to many relationship from
ParentMeetings to MeetingAttendees.

Table: ReportTypes
**ReportTypeID
ReportType

Table: StudentNotesAndReports
**NoteReportID
*StudentID (one-to-many with referential integrity enforced)
*ReportTypeID (one-to-many with referential integrity enforced)
ReportDate
Notes

good - if a Report is always about a single student

Table: CourseInfo
**CourseID
CourseName
MonthlyRate
SpecialRate

Table: CourseDetailsPerStudent
**StudentID
**CourseID
(The StudentID and CourseID are combined pks, with but I don’t know if this
is right and am unsure of the relationship between these fields and the
Student and Course tables)

The StudentID is a Foreign Key to Students; the CourseID is a Foreign
Key to CourseInfo; they jointly constitute the one and only Primary
Key; and the design is precisely correct.

RegistrationDate
RegistrationFee
TuitionFee
DiscontinuedDate
VacationDate


Table: Awards
**AwardID
AwardName
AwardDescription

Table: StudentAwards
StudentID (one-to-many with referential integrity enforced)
AwardID (one-to-many with referential integrity enforced)
AwardDate
Comments
Yep

Table: PaymentInfo
**PaymentID
*FamilyID (one-to-many with referential integrity enforced)
Mode
Date
Amount
ChequeNo


Table: PaymentAllocation
*PaymentID (one-to-many with referential integrity enforced)
*DepositID (not sure what relationship should be or if ref. int. should be
enforced)

Each Deposit presumably contains multiple PaymentAllocation records...
I'm a little vague about how these are related in real life though!

*CourseID (not sure what relationship should be or if ref. int. should be
enforced)

Are payments ALWAYS course related? You can leave a foreign key blank
but you cannot leave a primary key (or any field in a primary key)
null.

*StudentID (not sure what relationship should be or if ref. int. should be
enforced)

again... do you need to allocate payments (or parts of payments) to
students? If so, yes, enforce RI one to many.

Amount



Table: DepositInfo
**DepositID
DepositNumber
DepositDate

Hope this gets you done by Friday! I think it should; your table
design looks great and you're well on the way with forms and reports.

John W. Vinson[MVP]

 

[Guest]

Hi again,

I am trying to drag the student form into the family form, but can't seem to
make it work. Will keep trying and looking up how.

The province is mostly QC for Quebec. It's a local company, so will be the
default and I don't think I need a table of provinces for this reason.

There is only one address per student, so not an issue.

When I enforce RI, do I cascade edits and deletions?

I will need to "archive" family info into either another database or table
when the student discontinues courses, for historical reference and also if
they come back. any idea on that?

I will re-do the meetings table as suggested and see why now.

I still am not clear at all on the:

The StudentID is a Foreign Key to Students; the CourseID is a Foreign
Key to CourseInfo; they jointly constitute the one and only Primary
Key; and the design is precisely correct.

I seem to have a mental block about why this is.

Thanks!

 

[Guest]

I'm having difficulty with the relationship between the FamilyInfo and the
Student Info tables when creating a form and sub-form.

From what I can gather, I only have the FamilyID as a primary key (and
autonumber). I have then dragged it to a number field that I also called
FamilyID in the Student Info table. But, it doesn't seem to work. Do I need
to set the StudentID and the FamilyID as both pk,s in the Students Info
table, then make the one to many relationship from FamilyInfo to StudentInfo

I'm very confused with this.

THANKS"!

 

[John Vinson]

I'm having difficulty with the relationship between the FamilyInfo and the
Student Info tables when creating a form and sub-form.

From what I can gather, I only have the FamilyID as a primary key (and
autonumber). I have then dragged it to a number field that I also called
FamilyID in the Student Info table. But, it doesn't seem to work. Do I need
to set the StudentID and the FamilyID as both pk,s in the Students Info
table, then make the one to many relationship from FamilyInfo to StudentInfo

I'm very confused with this.

What "doesn't work"? The StudentID is the Primary Key of the
StudentInfo table; the FamilyID is the Primary Key of the FamilyInFO
table. There would be a (Long Integer) FamilyID field in the
StudentInfo table (in addition to its own StudentID primary key);
you'ld create the relationship by dragging FamilyInfo.FamilyID to
StudentInfo.FamilyID and enforcing RI.

You would not need to invoke Cascade Updates - since an autonumber
field cannot be edited, there will never be any updates to cascade.
I'd be dubious about Cascade Deletes as well; if you set it, then
deleting a Family will delete your *entire history* of that student
and everything about that student, including your financial records!


John W. Vinson[MVP]

 

[Guest]

Hi John,

Thank you for your response. I understand the pk and fk, I think. Where
it's not clear to me is how the two fks make up a pk. In other words, do I
select both fields and click the pk button when in design view?

I'm also having an awful time with combo boxes. I thought that it would be
smart to break the tables down further - I have a general familyinfo table, a
familycontact table with the names of the family members and their phone
numbers. I thought I would be smart and create a table called PhoneTypes
where I could list home, work, cell, etc. However, when it comes to creating
a form to input this, I have tried to add a combo box that draws the data
from the PhoneTypes table and then places the value selected in the
FamilyContact table in the field called PhoneTypes, but it doesn't work.

Each time I enter the data, then close the form, it re-opens as blank and
looks as though a filter is applied. I have search the Internet and looked
at Help but don't understand this.

I'm sure it's something very simple that I'm missing and I am starting to
feel like quite the idiot with this.

Thank you again, John, for your help and your patience. (Although I'm sure
the latter is running out )

Dee

 

[John Vinson]

Hi John,

Thank you for your response. I understand the pk and fk, I think. Where
it's not clear to me is how the two fks make up a pk. In other words, do I
select both fields and click the pk button when in design view?

Yes, exactly. Shift-click if they're together in the table design
window, ctrl-click if they're separated - just so both are
highlighted. Then click the key icon.

I'm also having an awful time with combo boxes. I thought that it would be
smart to break the tables down further - I have a general familyinfo table, a
familycontact table with the names of the family members and their phone
numbers. I thought I would be smart and create a table called PhoneTypes
where I could list home, work, cell, etc. However, when it comes to creating
a form to input this, I have tried to add a combo box that draws the data
from the PhoneTypes table and then places the value selected in the
FamilyContact table in the field called PhoneTypes, but it doesn't work.

What "doesn't work"? What's the structure of the PhoneTypes table; the
RowSource of the combo; the Bound Column of the combo; the Control
Source; and what's the datatype of the field in the table to which the
combo is bound?

Each time I enter the data, then close the form, it re-opens as blank and
looks as though a filter is applied. I have search the Internet and looked
at Help but don't understand this.

I don't understand it either. Is there any code on the combo's
AfterUpdate event? Might you have used the combo box wizard to create
a combo box to find a record?

I'm sure it's something very simple that I'm missing and I am starting to
feel like quite the idiot with this.

Thank you again, John, for your help and your patience. (Although I'm sure
the latter is running out )

Not a bit of it!

However my time is (temporarily) running out - I'm leaving Saturday
morning early for a family trip, won't be back until Tuesday. You
might want to post a new thread to get another volunteer to help if
you need help before then.


John W. Vinson[MVP]