Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Select query: Help, please (XP)

    I'm not getting the results I need with a query I've built. The query uses the following tables:
    <UL><LI>tblStudentProgram

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select query: Help, please (XP)

    Why do you have tblStudentProgram in the query? If ProgramID is one of the fields in the query grid, it will cause duplication of records, and if you don't, the table is not needed.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Select query: Help, please (XP)

    Further to what Hans suggested,

    Do you have tblStudentProgram there to give you the Student Name (using the lookup)?

    Replace it by tblStudents to give you the name of the student.
    Regards
    John



  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Select query: Help, please (XP)

    Hans, that's a fair question...one I've asked myself, matter o' fact. Here's what I'm trying to do:

    You might recall a situation you helped me with a few weeks ago. (I certainly would not expect you to remember anything that far back. It's in the thread that starts with <post#=360893>post 360893</post#>. Briefly: I have a report that displays the courses an employee must complete in our learning lab, and the list of courses is specific to the employee's job. The Detail section displays the list of courses: medium (videotape, CD-ROM), course number and title, the credit value in clock hours. There's also a "field" for the date of completion, but, for now, the "field" is nothing more than a blank space on the paper copy of the report. The lab coordinator pencils in the date. A partner and I have been trying for days to add a completion date to the query that drives the report, but so far I get nothing but an alarm about "ambiguous outer joins." I barely understand the difference between an "inny" and an "outty," and this business of ambiguous joins has me baffled. The error message says something about constructing another query outside the main one and then adding that subquery to the main one.

    OK...that's what I'm trying to do here, and it seems to me (with my limited understanding of query construction) that I need to include the ProgramID in this mix. I need these queries to answer these questions:
    1. <LI>What is the student's training program and what courses appear in that program?
      <LI>Which courses has the student completed?
      <LI>When did he complete them?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Select query: Help, please (XP)

    That's not quite what I'm trying to do. Please see my reply to Hans's post.

    And thanks for your response!

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Select query: Help, please (XP)

    It seems to me that the problem here is that the requirements for an answer to question 1 are different to the requirements for the other two.

    To answer this " 1. What is the student's training program and what courses appear in that program?" you need to list a course many times if it is part of a number of programs, whereas for 2 and 3, you only want each course listed once.
    Regards
    John



Posting Permissions

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