Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Distinct Records

    Hi,
    I have a query in which a table of file IDs links to another table with a list of employee names. When the query runs, it comes like this

    File_ID Employee Name
    001 Pam
    001 Trish Stratus
    001 Carmen E.

    There is nothing wrong with this, (since those three employees worked on the same file) but how do i make the query give me DISTINCT file ids and not repeated files?

    Thx

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Distinct Records

    Originally posted by Jerrie
    Hi,
    I have a query in which a table of file IDs links to another table with a list of employee names. When the query runs, it comes like this

    File_ID Employee Name
    001 Pam
    001 Trish Stratus
    001 Carmen E.

    There is nothing wrong with this, (since those three employees worked on the same file) but how do i make the query give me DISTINCT file ids and not repeated files?

    Thx

    Code:
    select distinct 
    f.file_id, e.emp_name
    from file f, employee e
    where f.file_id = e.file_id

    I hope I understood what you wanted. If not, please elaborate on what you consider "repeated files"

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    As you see in my eg, 001 is repeated three times becus there were three employees workin on that file;

    i just want it to show 001 file once;

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    comeon, is there no one who can do this?
    pls,

    Thanx

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Jerrie
    comeon, is there no one who can do this?
    pls,

    Thanx

    First of all no one on this forum gets paid to help you, so CALM yourself down.

    You are not making yourself clear:

    You state you want distinct Files, yet it is perfectly acceptable to show 3 users working on the same file.

    How can you show a file once, but at the same time have it be ok for 3 users to work on it.

    Are you trying to get an output like this?

    File_ID --------- Name
    001 ---------- Pam, Trish, Carmen E.
    002 ---------- Chris, ETC
    003 ---------- Blah

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you are SELECTing as you say, you are getting DISTINCT records (cos you have file & name in the query, both are considered to determine DISTINCTness).

    drop the name!

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    lol sorry if i got a little too antsy
    the query i showed u was what i wanted changed
    i just want it to show

    FileID Name
    001 Trish

    or
    001 Pam

    as long it shows the record 001 once, thats all the matters
    i dont want it to show three times

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Jerrie
    lol sorry if i got a little too antsy
    the query i showed u was what i wanted changed
    i just want it to show

    FileID Name
    001 Trish

    or
    001 Pam

    as long it shows the record 001 once, thats all the matters
    i dont want it to show three times

    Its ok - The only question I have is this :

    How do you pick which name you want? If it doesnt matter, try this:

    select f.file_id, max(e.ename)
    from file f, employee e
    where f.file_id = e.file_id
    group by f.file_id

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if it doesn't matter: drop it from the query entirely!

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    the method you suggested ss659 doesn't work since i have INNER JOINED the two tables together;

    is there another method? how bout a module that checks the previous identification number and then verifies it?

    and what do you mean drop it? please explain.

    Thankx a bunch!

  11. #11
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Jerrie
    the method you suggested ss659 doesn't work since i have INNER JOINED the two tables together;

    is there another method? how bout a module that checks the previous identification number and then verifies it?

    and what do you mean drop it? please explain.

    Thankx a bunch!

    He means since you dont really care what the name is, why include it in the query to begin with? I am in agreeance with that... Doesnt make much sense to me to arbitrarily pick one name just to prove someone worked on it. It would make more sense to just select distinct file names, because presumably the file_id wouldnt be in the employee table unless an employee actually worked on it.

    And I beg to differ that my query doesnt work. Take out the Access written crap of inner join, and use the syntax I provided in the previous post. I dont see how a MAX function would not work here? It simply is grouping all of the employees by file_id, and getting the employee with the name furthest down the alphabet.

    What is the error that you are getting?

  12. #12
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    It works! but since i'm not that great at SQL, how would i make it work in design view? How do u create it in design view without using inner joins; i have another query to do similar for where the links go through three tables.

  13. #13
    Join Date
    Jan 2004
    Posts
    492
    Thats funny b/c Im not very good at Access Personally what I would do is design it the way you have been, but then switch over to SQL view and make some modifications until you are confident enough with writing your own SQL.

    An inner join is simply where a value in one table = value in another table.

    So I would suggest taking out the inner join syntax it writes for you, and replace it with the syntax like I gave an example by. You can use my example for three tables as well. You will notice I use an alias before the column names in the select statement. This makes it easier to read than putting the entire table name in there.

    Then all you do is put the alias directly after the table name in the FROM statement. No commas between table and alias.

    In the WHERE statement, you list the columns from each table which matchup to the column in the other table. Make sure you have a group by clause listing the other columns besides the max one.

    For 3 tables observe:

    Code:
    SELECT f.file_id, d.dept_name, max(e.emp_name)
    FROM file f, dept d, emp e
    WHERE f.file_id = e.file_id
    and e.dept_id = d.dept_id
    GROUP BY f.file_id, d.dept_name
    These are all inner or simple joins. I think you'll agree that this code is a lot simpler to digest than the one Access provides. Hope this helps and let me know if you need further help!
    Last edited by ss659; 02-13-04 at 19:58.

Posting Permissions

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