Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    182

    Unanswered: Select only records that have a unique ID

    How do I do this in Access Query? I have tried using Distinct but that only works if you are only selecting one field. i need to select a number of fields.

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    All records to my knowledge require a primary key. I do not understand your question really. Please elaborate a bit more and maybe we can help you.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Apr 2004
    Posts
    182
    I have a form showings drawings with and ID which is not unique as each drawing can have many revisions but I only want to show the latest drawing in my form as I am showing the revisions elsewhere.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by livvie
    How do I do this in Access Query? I have tried using Distinct but that only works if you are only selecting one field. i need to select a number of fields.
    DISTINCTROW ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Apr 2004
    Posts
    182
    DISTINCTROW wont work as each row has a dofferent revision number.
    When I type in DISTINCTROW in the SQL pane of Access it keeps giving me the error invalid column name DISTINCTROW - it seems to accept DISTINCT ok

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by livvie
    DISTINCTROW wont work as each row has a dofferent revision number.
    When I type in DISTINCTROW in the SQL pane of Access it keeps giving me the error invalid column name DISTINCTROW - it seems to accept DISTINCT ok
    SELECT DISTINCTROW Field1, Field2, ... ,FieldXXX FROM SomeTable;

    Doesn't work? Access Version? Can you post the query that you have?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Apr 2004
    Posts
    182
    Access 2003 Project
    SELECT tblDrawings.CTD_Number, tblDrawings.CID, tblDrawings.Date, tblDrawRev.Rev_No, tblPartNumber.Part_Name, tblPartNumber.Part_no
    FROM tblDrawings INNER JOIN
    tblDrawRev ON tblDrawings.CTD_Number = tblDrawRev.CTD_Number AND tblDrawings.Part_No = tblDrawRev.Part_no AND
    tblDrawings.Rev_No = tblDrawRev.Rev_No INNER JOIN
    tblPartNumber ON tblDrawings.Part_No = tblPartNumber.Part_no

    When I try to enter DISTINCTROW i get a syntax error

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    make a query that includes all the stuff you want unique: use DISTINCT
    make another query looking at the first query and the source table(s)/query(ies) to pull the remaining fields you need.

    (rudy will no doubt now come up with a single query solution!)


    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Apr 2004
    Posts
    182
    My query is my record source ! Does this make any difference

Posting Permissions

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