Pulling Data from multiple columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a report that will let me group data from multiple columns
in a table onto a report.

In my table I have:

Name
Subject 1
Subject 2
Subject 3

Now in Subjects 1,2,3 there will be some of the same values ie. French,
History etc So in my report I want group headings that will show all of the
Subjects and underneath that I want it to display all the names that are
doing these subjects.

Can anyone help?
 
I want to create a report that will let me group data from multiple columns
in a table onto a report.

In my table I have:

Name
Subject 1
Subject 2
Subject 3

Now in Subjects 1,2,3 there will be some of the same values ie. French,
History etc So in my report I want group headings that will show all of the
Subjects and underneath that I want it to display all the names that are
doing these subjects.

Can anyone help?
Click to expand...

Use a Crosstab Query as the basis of the Report. There's a Crosstab
Query wizard that can help you set this up.

John W. Vinson[MVP]
 
1foxi,
Your primary problem is your table design. It's preventing you from easily "grouping"
on Subject beacuse the Subject values are in 3 separate fields.
What would happen if a person had 4 Subjects, or 5 or 6. You'll have to add a Subject
field to your table each time, and then rework all your forms. queries, and reports to
deal with the new field.

(using example names/objects...)
StudentName to Subject should be a One to Many relationship... tblStudents to tbl
Subjects, related by a unique "key" field value.

tblNames should contain a unique key identifier for each person, say a StudentID.
(autonumber field)
tblSubjects should have just one field for StudentID, and one for Subject, and all
Subjects for all students will be entered in that table.

tblStudents
StudentID StudentName
123 John Smith
341 Mary Jones

tblSubjects
StudentID Subject
341 English
341 French
123 Algebra
341 Algebra
123 Economics

Build a form with tblStudents as the RecordSource for the main form.
Build a subform with tblSubjects as the RecordSource.
Relate them Parent/Child via the StudentID. Now you can enter any number of Subjects
against any Student, and build up your data as indicated above.

Now... you're ready to design a report where you group on Subject, and just those
Students taking that Subject will be listed under that.

It is too cumbersome via email to go into all the details, but these are the basics.
 
Back
Top