Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    90

    Question Unanswered: Query Writing Help - Seems easy but how?

    I have a very simple problem but I cannot seem to find a solution. I have a table with the values of a seminar and who gave it. Some times more than one person gives a seminar. So here is my table data:

    Name SEM_ID
    Joe Cool 1234
    Josh Brown 1234
    Billy Cundiff 1235
    Ron Burgandy 1236


    So Joe and Josh both gave seminar 1234. Now I need to write a query that does this.

    Gets all the seminar ID's (so there is no more than 1 of the same ID) and gets the Name of each of the instructors. If there is more than 1 person who gave the seminar then arbitrarily chose one of the people and the corresponding ID.

    So I want the query to produce something like this:

    SEM_ID Name
    1234 Joe Cool
    1235 Billy Cundiff
    1236 Ron Burgandy

    but I can only get soemthing like this (notice there are 2 instructors of 1 seminar- I DO NOT WANT THIS! .

    1234 Joe Cool
    1234 Josh Brown
    1235 Billy Cundiff
    1236 Ron Burgandy

    I have tried SELECT DISTINCT but if I want to get the name of one of the instructors then it does not work.... How can I do this? If I even can. Thanks

  2. #2
    Join Date
    Jun 2004
    Posts
    6
    Add a Yes/No (boolean) field [PrimInstr] to your table
    with default set to Yes. When adding seminars with
    more than one instructor, specify which is the primary
    instructor. Your query SQL then would become something
    like:
    Code:
    SELECT tblSem.SemID, tblSem.Instr
    FROM tblSem
    WHERE (((tblSem.PrimInstr)=Yes));
    HTH - Bob

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Hi lskuff,

    Just to get a better look at what you have, how do you have your tables structured? Is it all in just one table or do you have multiple tables you're dealing with? Reason I ask is because how you structure your tables will determine how easily you can get the data out that you wish in easy logical manners. It's all in how you first set up your foundation in the beginning. Without seeing it sounds as if you could have at least 2 tables easily.

    Bud

  4. #4
    Join Date
    Aug 2004
    Location
    Abu-Dhabi, U.A.E
    Posts
    5
    If you don't care about which instructor is selected you can use this SQL:

    SELECT tblSem.SemID, Max(tblSem.Instr) AS Instructor
    FROM tblSem
    GROUP BY TblSem.SemID;

    HTH

  5. #5
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Hi.

    It sounds like you only have one table. Bud is right. You should start with two tables: one for seminars, one for instructors.

    You said: "Some times more than one person gives a seminar."

    Set up a one-to-many relationship between your two tables (one seminar may have many instructors).

    The big question is, can an instructor give different seminars? In other words, if an instructor may only teach one seminar, you only need two tables. But, if an instructor may teach more than one seminar (at different times, of course), then you need a third table!

    wazz

  6. #6
    Join Date
    May 2004
    Posts
    90
    Well about the multiple tables. I have a seminar table, an instructor table, and a seminarInstructor table.

    Seminar table is where all the sminar info held, instructor is where all the instructor info is held, and seminarInstructor is where all the instructors who gave seminars and which seminar info.

    I think that one of the above queries will work. Thanks!

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Add an instructor id to your seminar table, that is your primary instructor.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Thanks wazz,

    Sometimes need that lil extra umph to get that point across. But seems all is going in the right direction now.

    have a nice one,
    Bud

  9. #9
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Hi again. I was just rereading your question because i thought I might be misinterpreting it. I think I am. I just fooled around with a composite table of mine (like the SeminarInstructor table of yours) in the QBE grid and found that if I 'Group By' the first field in the grid (SEM_ID in your case) and use 'Min' for the second field in the grid ('Name' in your case) I get all of the ID's from the first field and only one of many entries from the second field. Is that what you're after?
    wazz

Posting Permissions

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