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

    Unanswered: DISTINCT Records (Again)

    yes, i figured how to use the max function; however, yet another problem looms in view;

    For e.g

    FileID_____FamilyID
    001______ 0023
    002 ______0023
    004______0023
    007______0050
    008______0050

    A group of files are assigned the same family ID, they belong to the same family; i would like to just show DISTINCT FamilyIDs...so no repetitions;

    like this

    001 0023
    007 0050

    the filenumber selection does not matter;
    as long as there are no repeating familyIDS

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    In the query group by family ID you wish to see distinct rows for then if you do not care about the other field then specify first of file ID.

    To do this in the QBE window put the family id in the first column and file ID in the second. Then select from the view menu "Totals" and specify Group by (default) in the first column and select first in the second. If more than one family id comes up then open the query to the SQL view and add "Distinct" after "Select".
    KC

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    in SQL do something like this:

    Code:
    SELECT min(file_id), family_id
    from your_table
    group by family_id
    I personally think this is easier than changing any options - just pop this in and you will get one row per family.

  4. #4
    Join Date
    Oct 2012
    Posts
    1

    Combine rows that has the same ID

    Hi,

    I badly need anyone's help. I have currently created a simple access tables with the following fields and sample data:

    PresenceTbl:

    OrgID OfficialName DOI Website
    1234 xyz company 1/2/90 www.xyzco.com
    1235 fdsaf
    1344 2/15/80 Thinking Objects GmbH
    1543 pen co 3/01/90 penco.co - domain expired

    I created a query which will display a "Fail" if there are blank fields for a specific Org_ID and "pass" if not blank. It displays as follows:

    OrgID OfficialName DOI Website
    1234 pass pass pass
    1235 pass fail fail
    1344 fail pass pass
    1543 pass pass pass

    There is a related table which I called Validity tbl which displays fields with errors for each OrgID. But not all OrgId's in the Validity tbl are present in the presence tbl. See table below:

    OrgID FieldIssue Type
    43432 Official Name Inconsistent
    1234 Official Name Inconsistent
    1234 Web URL Invalid Characters
    1344 DOI Date not accepted

    I want to create a query wherein, there will be a lookup of ORg IDs from Presence Tbl in the Validity Tbl. The org IDs with the same fields as in the Presence tbl. it will show a Fail if that org ID exist in the Validity table. The "Fail" comment will show in the specific column mentioned in the Field issue column. See below:

    OrgID Official Name DOI Website
    1234 Fail NA Fail
    1235 NA NA NA
    1344 NA Fail' Na
    1235 NA NA NA


    I tried doing this in MS Excel using Vlookup and it worked but it was really slow. I will be working on some 200thousand records and Access is pretty much quicker (as I tried presence and it worked with no wait time).

    Need anyone's help. ASAP. Thank you.

  5. #5
    Join Date
    Oct 2012
    Posts
    3
    Quote Originally Posted by Jerrie View Post
    yes, i figured how to use the max function; however, yet another problem looms in view;

    For e.g

    FileID_____FamilyID
    001______ 0023
    002 ______0023
    004______0023
    007______0050
    008______0050

    A group of files are assigned the same family ID, they belong to the same family; i would like to just show DISTINCT FamilyIDs...so no repetitions;

    like this

    001 0023
    007 0050

    the filenumber selection does not matter;
    as long as there are no repeating familyIDS
    Why don't you try this SQL:
    "Select Distinct Table.FamilyID From Table"

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ndp View Post
    Why don't you try this SQL:
    "Select Distinct Table.FamilyID From Table"
    And what do you do with the FileID column?
    Have a nice day!

Posting Permissions

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