Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Distinct Records

Discussion in 'Information Technology' started by TheRook, Jul 28, 2006.

  1. TheRook

    TheRook
    Expand Collapse
    Guest

    I currently have a query built but am now wanting to only show specific
    distinct results.

    Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'

    What i am wanting to show is only the PART NO.'s were ALL INCLUDE_IN_SFDC = Y

    For example:
    PART_NO. INC_SFDC
    123 Y
    123 Y
    123 N
    123 Y
    123 Y
    666 Y
    666 Y
    666 Y

    the result would only show 666, as the 3rd instance of 123 as INC_SFDC as N.
    Obiously there ar other colums involved but are only for results not to
    query against.

    How can this be done?

    Thanks in advance
     
  2. Loading...


  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    SELECT TblRook.PART_NO
    FROM TblRook
    GROUP BY TblRook.PART_NO
    HAVING Min(TblRook.INC_SFDC)=DMax("[INC_SFDC]","TblRook");

    I haven't tested for Nulls.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "TheRook" wrote:

    > I currently have a query built but am now wanting to only show specific
    > distinct results.
    >
    > Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'
    >
    > What i am wanting to show is only the PART NO.'s were ALL INCLUDE_IN_SFDC = Y
    >
    > For example:
    > PART_NO. INC_SFDC
    > 123 Y
    > 123 Y
    > 123 N
    > 123 Y
    > 123 Y
    > 666 Y
    > 666 Y
    > 666 Y
    >
    > the result would only show 666, as the 3rd instance of 123 as INC_SFDC as N.
    > Obiously there ar other colums involved but are only for results not to
    > query against.
    >
    > How can this be done?
    >
    > Thanks in advance
    >
     
  4. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "TheRook" <TheRook@discussions.microsoft.com> wrote in message
    news:76D3094D-60C4-483B-A7FF-C36CF75C15EC@microsoft.com
    > I currently have a query built but am now wanting to only show
    > specific distinct results.
    >
    > Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'
    >
    > What i am wanting to show is only the PART NO.'s were ALL
    > INCLUDE_IN_SFDC = Y
    >
    > For example:
    > PART_NO. INC_SFDC
    > 123 Y
    > 123 Y
    > 123 N
    > 123 Y
    > 123 Y
    > 666 Y
    > 666 Y
    > 666 Y
    >
    > the result would only show 666, as the 3rd instance of 123 as
    > INC_SFDC as N. Obiously there ar other colums involved but are only
    > for results not to query against.
    >
    > How can this be done?
    >
    > Thanks in advance


    It sounds like you want a query that specifies

    SELECT <some fields> FROM YourTable
    WHERE [PART_NO] Not In
    (SELECT [PART_NO] FROM YourTable
    WHERE [INCLUDE_IN_SFDC_Y_N] = 'N')

    It's not clear to me from your description whether that query should
    also have a DISTINCT or GROUP BY clause. Also, I'm not sure what the
    real field names are. But maybe you can take it from here.

    Note: the Jet database engine doesn't handle the "Not In" construct
    very efficiently. If you find this version of the query to run
    unacceptably slowly, there are other ways to frame it that optimize
    better. But this is the cleanest way to express it.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  5. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    Forget about my first effort. It's lacking a couple of things. Try this:

    SELECT TblRook.PART_NO
    FROM TblRook
    GROUP BY TblRook.PART_NO
    HAVING (((Min(TblRook.INC_SFDC))
    =DMax("[INC_SFDC]","TblRook","PART_NO = " & [PART_NO])
    AND (Min(TblRook.INC_SFDC))='Y'));
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "TheRook" wrote:

    > I currently have a query built but am now wanting to only show specific
    > distinct results.
    >
    > Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'
    >
    > What i am wanting to show is only the PART NO.'s were ALL INCLUDE_IN_SFDC = Y
    >
    > For example:
    > PART_NO. INC_SFDC
    > 123 Y
    > 123 Y
    > 123 N
    > 123 Y
    > 123 Y
    > 666 Y
    > 666 Y
    > 666 Y
    >
    > the result would only show 666, as the 3rd instance of 123 as INC_SFDC as N.
    > Obiously there ar other colums involved but are only for results not to
    > query against.
    >
    > How can this be done?
    >
    > Thanks in advance
    >
     
  6. jahoobob via AccessMonster.com

    Guest

    Try this:
    SELECT DISTINCT Table.PART_No, Table.INC_SFD
    FROM Table
    WHERE (((Table.INC_SFD)="Y"));

    TheRook wrote:
    >I currently have a query built but am now wanting to only show specific
    >distinct results.
    >
    >Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'
    >
    >What i am wanting to show is only the PART NO.'s were ALL INCLUDE_IN_SFDC = Y
    >
    >For example:
    >PART_NO. INC_SFDC
    >123 Y
    >123 Y
    >123 N
    >123 Y
    >123 Y
    >666 Y
    >666 Y
    >666 Y
    >
    >the result would only show 666, as the 3rd instance of 123 as INC_SFDC as N.
    >Obiously there ar other colums involved but are only for results not to
    >query against.
    >
    >How can this be done?
    >
    >Thanks in advance


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
     
  7. TheRook

    TheRook
    Expand Collapse
    Guest

    As you can probably tell I am new to Access. I have not told you that all
    the data is from 4 different tables that are linked. PART_No is in
    DMCS_PLANNING_OP_SEQ and INCLUDE_IN_SFDC is in DMCS_PLANNINGS_STAGE.

    Please find below my current query, which results ALL records, as being new
    to it can not understand where I put your segestions:

    SELECT DMCS_PLANNINGS_OP_SEQ.PART_NUMBER,
    DMCS_PLANNINGS_OP_SEQ.PLANNINGS_vc, DMCS_PLANNINGS_OP_SEQ.CENTRE,
    DMCS_PLANNINGS_OP_SEQ.OP_SEQ, DMCS_PLANNINGS_STAGE.STAGE,
    DMCS_CENTRES.INCLUDE_IN_SFDC
    FROM ((DMCS_PLANNINGS_OP_SEQ INNER JOIN DMCS_PLANNINGS_STAGE ON
    (DMCS_PLANNINGS_OP_SEQ.PLANNINGS_vc = DMCS_PLANNINGS_STAGE.PLANNINGS_vc) AND
    (DMCS_PLANNINGS_OP_SEQ.PART_NUMBER = DMCS_PLANNINGS_STAGE.PART_NUMBER)) INNER
    JOIN DMCS_PLAN001_OP_SEQ ON (DMCS_PLANNINGS_OP_SEQ.PART_NUMBER =
    DMCS_PLAN001_OP_SEQ.PART_NUMBER) AND (DMCS_PLANNINGS_OP_SEQ.OP_SEQ =
    DMCS_PLAN001_OP_SEQ.OP_SEQ)) INNER JOIN DMCS_CENTRES ON
    DMCS_PLAN001_OP_SEQ.CENTRE = DMCS_CENTRES.CENTRES
    WHERE (((DMCS_CENTRES.INCLUDE_IN_SFDC)="Y"))
    ORDER BY DMCS_PLANNINGS_OP_SEQ.PART_NUMBER, DMCS_PLANNINGS_OP_SEQ.OP_SEQ;


    "jahoobob via AccessMonster.com" wrote:

    > Try this:
    > SELECT DISTINCT Table.PART_No, Table.INC_SFD
    > FROM Table
    > WHERE (((Table.INC_SFD)="Y"));
    >
    > TheRook wrote:
    > >I currently have a query built but am now wanting to only show specific
    > >distinct results.
    > >
    > >Two for the resulting columns are 'PART_No.' and INCLUDE_IN_SFDC_Y_N'
    > >
    > >What i am wanting to show is only the PART NO.'s were ALL INCLUDE_IN_SFDC = Y
    > >
    > >For example:
    > >PART_NO. INC_SFDC
    > >123 Y
    > >123 Y
    > >123 N
    > >123 Y
    > >123 Y
    > >666 Y
    > >666 Y
    > >666 Y
    > >
    > >the result would only show 666, as the 3rd instance of 123 as INC_SFDC as N.
    > >Obiously there ar other colums involved but are only for results not to
    > >query against.
    > >
    > >How can this be done?
    > >
    > >Thanks in advance

    >
    > --
    > Message posted via AccessMonster.com
    > http://www.accessmonster.com/Uwe/Forums.aspx/access/200605/1
    >
     

Share This Page