Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009

    Unanswered: NEED IMMEDIATE Assistance with complex report

    Database structure:

    Table: Students
    Fields: Student ID
    Last Name
    First Name
    Grade Level

    Table: Classes
    Fields: ClassCode

    Table: ClassAssignments
    Fields: ClassCode
    Student ID

    Relationships: Tabletudentstudent ID
    Relates to
    Table:ClassAssignmentstudent ID

    Relates to

    Each student has 2 classes assigned to them. I need a report that will pull all students from a class and will display their other class.

    Example: I need a list that shows all 1st Grade review Period 1 students (First Name, Last Name and Grade) and then their Period 2 class.

    Is there ANY way I can do this? Please help!

    Thank you

  2. #2
    Join Date
    May 2005
    Welcome to the forums

    I believe this can be done with a SELECT query and some creative criterias. What have you got so far?

    By the way, have to ask, is this for a class project?
    Me.Geek = True

  3. #3
    Join Date
    Jun 2009
    Well to be honest I have nothing. I set up the db for my mother she is the secretary for summer school and i have made it so it can do more things than her old db until we ran into this problem and it is an important needed report. I can't figure appropriate criterias for it to pull the information needed.

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    Without know what fields/tables represent - "I need a list that shows all 1st Grade review Period 1 students (First Name, Last Name and Grade) and then their Period 2 class", my guess is that you'll have possibly 2 (or 3 queries).

    Using Query Designer! (much easier versus trying to code this directly), Create your first query which has the criteria in it to just retrieve the records to show 1st Grade Students Making sure the ID field is in the query. Then create a 2nd query which has the ID field in one of the tables (buy not the same table used in the 1st query - ie. the table the 1st query would join to) with criteria for their Period 1 or 2 classes (again - you need the ID field which is going to link both these queries together in the final query (where you'll add both the 1st and 2nd query linking by that ID field). You "may" be able to just link the 2nd query into the 1st query but I can't say for sure without more info.

    IF the "1st Grade review period" and "Period 2" data is in the same table (ie. classes), then just have 1 query with any tables that link that back to the students table, and then put criteria in that query where the class value = "Period 1" OR "Period 2" (along with criteria where Grade = 1st). But it's confusing on where your data is stored (field/tables) for your request without more info.

    Try doing this in the query designer and then select "SQL" (versus Design/Open) to then view the sql statement.
    Last edited by pkstormy; 06-05-09 at 02:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts