Query Won't Work

[dee]

Hi,

I have a student database and wish to generate a report based on a query
that contains:

Student Last Name (from Family Info table)
Student First Name (from Student Info table)
Has Registered (from Student Info table (a yes-no field)
Registration Date (from Student Info table - this is a one-time per student
field)
Course Name (from Courses table)

(if I just use these fields and tables, no problem. It's when I add the
next table that no data appears)

Discontinued Date (from Course Details table - this is a date they stopped a
particular course; they may still be taking other courses)

My relationships are:

FamilyID PK in Family Info table - one to many to FamilyID field in Student
Info table, referential integrity enforced

StudentID PK in Student Info table to StudentID in Course Details table,
ref. int. enforced
CourseID PK in Courses table to Course Details table, ref. int. enforced

The StudentID and CourseID fields in Course Details together form a PK

What am I missing? Help!

Thanks!!

 

[Jeff Boyce]

Dee

Not clear how this is a "tables" question...

If you have used an "equi-"join to the CourseDetails table, you will only
find records that match in both sides of the join.

You can change the join properties to show all of the first side
(?CoursesTaken), and any of the CourseDetail on matching join fields.

It would help to see the SQL statement of your query.

Good luck

Jeff Boyce
<Access MVP>

 

[BruceM]

If the courses table is a listing of courses available to all students, then
each student can take many courses and each course can be taken by many
students, which adds up to a many-to-many relationship. This means that
there needs to be a junction table between the Course table and the Student
table to resolve the relationship. Perhaps that is the Course Detail table,
or what could perhaps be better described as the Enrollment table. A
junction table needs to contain as foreign keys the PKs from the tables on
either side. In this case it needs to contain StudentId and CourseID as
foreign keys, each one related one-to-many from the Student table and the
Course table. By the way, the point of separating first and last names into
separate tables eludes me, but anyhow.

tblStudent
StudentID (PK)
Personal Info.

tblCourses
CourseID (PK)
Course Description
Instructor, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (FK)
CourseID (FK)
StartDate
Withdrew (yes/no)
Any information specific to this student in this course

If you make a main form based on tblStudent and a subform (with default view
set to Continuous) based on tblEnrollment you will be able to list a
student's courses below that student's name and other personal information.

 

[dee]

Hi guys,

Thank you both for your answers.

I finally created a query that was based on two tables, plus another query:

SQL:
SELECT FamilyInfo.FamilyName, StudentInfo.StudentFirstName,
[StudentCourseInfo Sub-Query].CourseName, [StudentCourseInfo
Sub-Query].DiscontinuedDate, [StudentCourseInfo Sub-Query].VacationStartDate,
[StudentCourseInfo Sub-Query].VacationEndDate
FROM [StudentCourseInfo Sub-Query], FamilyInfo INNER JOIN StudentInfo ON
FamilyInfo.FamilyID = StudentInfo.FamilyID;

This seemed to work.

Regarding the question of why I have the last name in another table from the
Student Info, it's because I'm creating a database for a tutoring school
where a family may have many children attending lessons.

So, I have a Family Info table that contains basics, such as family ID,
name, address, etc. that I link to the Student Info table.

I now have another question, if you don't mind. Most courses are a standard
monthly rate, say $50.00. This means that each family will make a payment of
$50.00 x the number of children they send. However, some families will pay a
special flat rate - instead of $100, for example, they will pay $80 per
month. I'm not sure how to achieve this in the most effective manner.

Thanks again!

 

[BruceM]

I can't really help you with the money thing, and would not have jumped into
the thread in the first place had that been the question. I will add a
thought about Last Name coming from a different table than First Name.
Having a Family table makes sense, and I can understand that perfectly, but
does every child have the same last name as is used for the family name? You
would be better to have a separate Last Name field in the student table.

Hi guys,

Thank you both for your answers.

I finally created a query that was based on two tables, plus another query:

SQL:
SELECT FamilyInfo.FamilyName, StudentInfo.StudentFirstName,
[StudentCourseInfo Sub-Query].CourseName, [StudentCourseInfo
Sub-Query].DiscontinuedDate, [StudentCourseInfo Sub-Query].VacationStartDate,
[StudentCourseInfo Sub-Query].VacationEndDate
FROM [StudentCourseInfo Sub-Query], FamilyInfo INNER JOIN StudentInfo ON
FamilyInfo.FamilyID = StudentInfo.FamilyID;

This seemed to work.

Regarding the question of why I have the last name in another table from the
Student Info, it's because I'm creating a database for a tutoring school
where a family may have many children attending lessons.

So, I have a Family Info table that contains basics, such as family ID,
name, address, etc. that I link to the Student Info table.

I now have another question, if you don't mind. Most courses are a standard
monthly rate, say $50.00. This means that each family will make a payment of
$50.00 x the number of children they send. However, some families will pay a
special flat rate - instead of $100, for example, they will pay $80 per
month. I'm not sure how to achieve this in the most effective manner.

Thanks again!
--
Thanks!

Dee

If the courses table is a listing of courses available to all students, then
each student can take many courses and each course can be taken by many
students, which adds up to a many-to-many relationship. This means that
there needs to be a junction table between the Course table and the Student
table to resolve the relationship. Perhaps that is the Course Detail table,
or what could perhaps be better described as the Enrollment table. A
junction table needs to contain as foreign keys the PKs from the tables on
either side. In this case it needs to contain StudentId and CourseID as
foreign keys, each one related one-to-many from the Student table and the
Course table. By the way, the point of separating first and last names into
separate tables eludes me, but anyhow.

tblStudent
StudentID (PK)
Personal Info.

tblCourses
CourseID (PK)
Course Description
Instructor, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (FK)
CourseID (FK)
StartDate
Withdrew (yes/no)
Any information specific to this student in this course

If you make a main form based on tblStudent and a subform (with default view
set to Continuous) based on tblEnrollment you will be able to list a
student's courses below that student's name and other personal information.

 

[Jeff Boyce]

Dee

You will not get as many "eyes" on your question if you keep it way down
this thread. I'd recommend re-posting, asking this new question in a new
thread, to get maximum exposure.

Jeff Boyce
<Access MVP>

Hi guys,

Thank you both for your answers.

I finally created a query that was based on two tables, plus another
query:

SQL:
SELECT FamilyInfo.FamilyName, StudentInfo.StudentFirstName,
[StudentCourseInfo Sub-Query].CourseName, [StudentCourseInfo
Sub-Query].DiscontinuedDate, [StudentCourseInfo
Sub-Query].VacationStartDate,
[StudentCourseInfo Sub-Query].VacationEndDate
FROM [StudentCourseInfo Sub-Query], FamilyInfo INNER JOIN StudentInfo ON
FamilyInfo.FamilyID = StudentInfo.FamilyID;

This seemed to work.

Regarding the question of why I have the last name in another table from
the
Student Info, it's because I'm creating a database for a tutoring school
where a family may have many children attending lessons.

So, I have a Family Info table that contains basics, such as family ID,
name, address, etc. that I link to the Student Info table.

I now have another question, if you don't mind. Most courses are a
standard
monthly rate, say $50.00. This means that each family will make a payment
of
$50.00 x the number of children they send. However, some families will
pay a
special flat rate - instead of $100, for example, they will pay $80 per
month. I'm not sure how to achieve this in the most effective manner.

Thanks again!
--
Thanks!

Dee

If the courses table is a listing of courses available to all students,
then
each student can take many courses and each course can be taken by many
students, which adds up to a many-to-many relationship. This means that
there needs to be a junction table between the Course table and the
Student
table to resolve the relationship. Perhaps that is the Course Detail
table,
or what could perhaps be better described as the Enrollment table. A
junction table needs to contain as foreign keys the PKs from the tables
on
either side. In this case it needs to contain StudentId and CourseID as
foreign keys, each one related one-to-many from the Student table and the
Course table. By the way, the point of separating first and last names
into
separate tables eludes me, but anyhow.

tblStudent
StudentID (PK)
Personal Info.

tblCourses
CourseID (PK)
Course Description
Instructor, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (FK)
CourseID (FK)
StartDate
Withdrew (yes/no)
Any information specific to this student in this course

If you make a main form based on tblStudent and a subform (with default
view
set to Continuous) based on tblEnrollment you will be able to list a
student's courses below that student's name and other personal
information.