data display problem

  • Thread starter Thread starter jemadison
  • Start date Start date
J

jemadison

I have a small school database with a one to many
relationship. Students and parents are linked by the
students Id field. On a report or query I would like to
display all the records relating to one Id number in one
row, horizontally. For instance, one student with two
parents would usually display a seperate record for each
parent beneath the student. I need to place the record
for the student and both parents on one line in a report.
Can anyone help?

thanks
 
jemadison said:
I have a small school database with a one to many
relationship. Students and parents are linked by the
students Id field. On a report or query I would like to
display all the records relating to one Id number in one
row, horizontally. For instance, one student with two
parents would usually display a seperate record for each
parent beneath the student. I need to place the record
for the student and both parents on one line in a report.
Can anyone help?

thanks
Click to expand...

I'd suggest: make a query and choose what records you want to display. Then
add 'DISTINCT' to the criteria of the student ID. (This shows only one
record for each unique ID). Then build your report based on the query.

hth,
-Gary
 
You'll need to add a field to the Parents table to store the relationship of
the parent to the student. Then write a query like the following:

SELECT DISTINCT Students.StudentName,
(select parentname
from parents
where parents.studentid =
students.studentid
and parents.relationship = "Father") AS
Father,
(select parentname
from parents
where parents.studentid =
students.studentid
and parents.relationship = "Mother") AS
Mother
FROM Students INNER JOIN parents ON Students.StudentID = parents.StudentID;
 
Back
Top