Results 1 to 6 of 6
  1. #1
    gahawy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    3

    Need a select statement

    I'm new to access, so I hope I can express my problem clearly.

    I have 3 tables

    1- [students]
    Student_code [this is the primary key]
    Student_Name
    Student_Cellular
    (and some other fields)


    2- [Courses]


    Course_Code [this is the primary key]
    Course_Name
    Course_hrs
    (Some other fields)

    3- [Completed_Courses]
    Transaction_code [this is the primary key]
    Student_Code
    Course_Code
    Grade
    (Some other Fields)


    Now, all what I need is to create a select statement in which I can select students from table 3 [completed_Courses] whith the a criteria indicating what courses the student completed and what courses the student in the result should not have completed. Also I need to have the results one line per student, using distinct maybe.

    any help?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you shouldn't select students from 3. There should be relationships in place here. are there?

    I see this question all the time, and the typical answer should (doesn't mean that it is) be something like:
    Code:
    select 1.student, 3.course, 3.coursetaken
    
    from 1 INNER JOIN 3 on 1.studentID = 3.studentID
    
    where 3.coursetaken = false
    that will give more than one line per student's classes that aren't taken, but that is the syntax for the proper way of querying this out.

  3. #3
    gahawy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    3
    Thanks for this quick reponse.

    Yes there is a relationship between the three tables using the primary key.

    Ok, I guess I was not clear enough.

    Lets assume that student X has attended 2 courses course A and course B.
    if I'm looking for someone who didn't attend course A and who attended Course B, student X should not appear in the result at all.

    Is that possible?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    of course it is. are you familiar with sql at all?

    the standard sql I have given can be manipulated to do just that. so yes, it is possible, to answer the question you asked.

    I suppose the example I gave doesn't need the WHERE clause, given your tables, but the idea is still the same. does that make sense?

  5. #5
    gahawy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2011
    Posts
    3
    I'm a Network admin since many years ago, but I'm new to programming and database, so I'm trying to figure out my way.

    I will try to translate your select statement, or may be you can give me a one that I can use directly to test.


    Thanks alot

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    well, you cannot return one line per student in this case, if the student has more than one class that is not finished. You want course names, right? If so, you can't do it with a dataset, but you can manipulate that data through forms/rpt's to make it look that way.

    as far as what you said you want, it's impossible to give you that because the tables and fields that you showed me doesn't give the information that someone would need to produce the answer. so here's what you can get, based on what you gave us:

    To get a list of your students and what courses they completed:
    Code:
    select students.student_code, Completed_Courses.course_code
    
    from students inner join Completed_Courses on
    
    Completed_Courses.student_code = students.student_code
    Now, if you wanted a COUNT of the number of the courses each student has completed, you CAN return one line per student:

    Code:
    select students.student_code, count(Completed_Courses.course_code) as Num_Completed
    
    from students inner join Completed_Courses on
    
    Completed_Courses.student_code = students.student_code
    
    GROUP BY students.student_code
    Your 'courses' table doesn't tell me much. There is no information given by you to help determine how an incomplete course is determined. The only thing that can be guessed, is that if a course is not in table 3, then the student didn't take it, but that isn't enough to determine whether or not the student should've taken it, or was signed up. Info about those sort of things would get the more advanced answer you want.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Check my Select Statement in Form
    By OMGsh Y did I say Yes in forum Forms
    Replies: 12
    Last Post: 12-07-2010, 02:13 PM
  2. Conditional Select Statement
    By shexe in forum Queries
    Replies: 4
    Last Post: 09-22-2010, 09:10 AM
  3. select statement
    By jellybeannn in forum Access
    Replies: 5
    Last Post: 08-13-2010, 05:21 AM
  4. Select statement syntax?
    By ksmith in forum Programming
    Replies: 3
    Last Post: 06-24-2010, 09:21 AM
  5. What is wrong with my SQL Select statement?
    By John2810 in forum Programming
    Replies: 2
    Last Post: 04-01-2010, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums