Report with relational tables?

[Kelly]

Hi Everyone,
I'm here a couple times a year and I always have to relearn when I start a
new project. This one is much harder than I've done before. Here is what I
am doing.
I am exporting data from our school software system to create a form that
parents will fill out to re-enroll their children in our school. I have made
4 tables. The primary one is for student information. I am using a student
id as the primary key. I made a second table with parent information, again
using student id to relate them, but not as the primary key. Many students
have up to 4 parents, so the student id is being used several times. My
third table is for emergency contacts, set us the same way as parent info.
The fourth table is for the medication that we are allowed to administer.
Each student could have up to 3 entries for this.

Question #1 -
Can I have 1 table related all 3 tables? This seems to be ok, but I can't
get the report to spit out the way I want it.

#2
I'm not doing a query since I basically did that before it came into Access,
our school software did that for me. Should I do one? And if so, what would
it be?

#3 - The report!
Help! Assuming I've done everything right so far, this shouldn't be too
hard. My report should be in 4 sections. The first section will have all
the student info. The second parent info, the third emergency contacts, and
the fourth medicine. I've tried all sorts of ways, I've made headers, I've
made footers, I've put stuff in the detail section, but I always end up with
duplicate data for most of it. I am trying to get one page per student, so I
know enough that my student id should be the page header. After that I am
lost.

This is my last resort before I try to find someone to hire. Am I in over
my head, or is there an easy way to explain this to me?

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

Hi Everyone,
I'm here a couple times a year and I always have to relearn when I start a
new project. This one is much harder than I've done before. Here is what I
am doing.
I am exporting data from our school software system to create a form that
parents will fill out to re-enroll their children in our school. I have made
4 tables. The primary one is for student information. I am using a student
id as the primary key. I made a second table with parent information, again
using student id to relate them, but not as the primary key. Many students
have up to 4 parents, so the student id is being used several times. My
third table is for emergency contacts, set us the same way as parent info.
The fourth table is for the medication that we are allowed to administer.
Each student could have up to 3 entries for this.

Question #1 -
Can I have 1 table related all 3 tables? This seems to be ok, but I can't
get the report to spit out the way I want it.

As you describe it, all tables are related to the Student table by Student_ID.

#2
I'm not doing a query since I basically did that before it came into Access,
our school software did that for me. Should I do one? And if so, what would
it be?

As a habit, I always use queries for my report record sources. It is not
necessary unless you are using multiple tables and need to join them.

#3 - The report!
Help! Assuming I've done everything right so far, this shouldn't be too
hard. My report should be in 4 sections. The first section will have all
the student info. The second parent info, the third emergency contacts, and
the fourth medicine. I've tried all sorts of ways, I've made headers, I've
made footers, I've put stuff in the detail section, but I always end up with
duplicate data for most of it. I am trying to get one page per student, so I
know enough that my student id should be the page header. After that I am
lost.

The easiest way to do this would be with sub reports. That is, you will
create a report for each table. The report with the student info should be
the master. Then in this master, you create sub report controls. To relate
the records in the record source of the sub reports, use the Link Master/Link
Child Fields properties to relate the records.

 

[Kelly]

Thank you so much. I had never heard about sub reports before yesterday.
(My Dummies book doesn't mention them.) I had to do a little more research

 

[Klatuu]

Glad I could help, Kelly.
Now do others a favor. Please rate the response as having answered your
question. Then it will remain in the list so others searching for a similar
answer will find it.