Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Pulling Data from multiple columns

Discussion in 'Information Technology' started by 1foxi, Jul 28, 2006.

  1. 1foxi

    1foxi
    Expand Collapse
    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?
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Fri, 16 Jun 2006 04:26:02 -0700, 1foxi
    <1foxi@discussions.microsoft.com> wrote:

    >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?


    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]
     
  4. Al Camp

    Al Camp
    Expand Collapse
    Guest

    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.
    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions

    "1foxi" <1foxi@discussions.microsoft.com> wrote in message
    news:75F6C30F-B87F-4B2A-A9C2-D1966A332FE1@microsoft.com...
    >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?
    >
     

Share This Page