Welcome to SPN

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

Sign Up Now!

Dropdown list in combobox showing duplicates

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

  1. Ricter

    Ricter
    Expand Collapse
    Guest

    Row Source Type: Table/Query
    Row Source:
    SELECT DISTINCT [tblShipments].[ID], [tblShipments].[Company] FROM
    tblShipments ORDER BY [Company];

    I'm seeing as many instances of Company X in the dropdown list as there are
    instances of Company X in the table... What am I missing here?

    tia
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News How The Left & Indian Nationalists Distorted The History Of Sikh Gadhar Movement - Breaking News Sep 21, 2016
    Interfaith Materialists And Its Discontents Interfaith Dialogues Jun 10, 2016
    Sikhi Quasi-Pragmatists And Quasi-Spiritualists, Care To Explain? Sikh Sikhi Sikhism Jun 6, 2016
    Cholesterol Is Finally Officially Removed From "naughty List" Health & Nutrition Feb 11, 2016
    Listening to Gurbani while driving?? Sikh Rehat Maryada Jan 13, 2016

  3. xRoachx

    xRoachx
    Expand Collapse
    Guest

    Hi Ricter -- In your statement, you are selecting distinct records of the ID
    not the Company. Since your table is titled Shipments, I'm quessing Company
    X can be listed multiple times while the ID is unique...there are some
    problems with this as well because you should not be storing the name of the
    company but an ID related to a company table.

    Anyhow, try this to correct your current issue:

    SELECT [tblShipments].[ID], [tblShipments].[Company] FROM
    tblShipments GROUP BY [Company] ORDER BY [Company];


    "Ricter" wrote:

    > Row Source Type: Table/Query
    > Row Source:
    > SELECT DISTINCT [tblShipments].[ID], [tblShipments].[Company] FROM
    > tblShipments ORDER BY [Company];
    >
    > I'm seeing as many instances of Company X in the dropdown list as there are
    > instances of Company X in the table... What am I missing here?
    >
    > tia
     
  4. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Your SQL Statement is looking for distinct ID/Name combos, so your DISTINCT
    clause will return each unique pair.

    123 Company X
    436 Company X
    854 Company X
    etc.

    I'm assuming you don't need the ID, since it's a ShipmentID. Remove that from
    your SQL and it should give you want you want.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "Ricter" <Ricter@discussions.microsoft.com> wrote ...
    > Row Source Type: Table/Query
    > Row Source:
    > SELECT DISTINCT [tblShipments].[ID], [tblShipments].[Company] FROM
    > tblShipments ORDER BY [Company];
    >
    > I'm seeing as many instances of Company X in the dropdown list as there are
    > instances of Company X in the table... What am I missing here?
    >
    > tia
     
  5. Ricter

    Ricter
    Expand Collapse
    Guest

    "You tried to execute a query that does not include the specified expression
    [ID] as part of an aggregate function."

    I believe I'm going to normalize this relationship properly. I can't
    believe I overlooked it during design. *sigh* At least it's still test data.

    "xRoachx" wrote:

    > Hi Ricter -- In your statement, you are selecting distinct records of the ID
    > not the Company. Since your table is titled Shipments, I'm quessing Company
    > X can be listed multiple times while the ID is unique...there are some
    > problems with this as well because you should not be storing the name of the
    > company but an ID related to a company table.
    >
    > Anyhow, try this to correct your current issue:
    >
    > SELECT [tblShipments].[ID], [tblShipments].[Company] FROM
    > tblShipments GROUP BY [Company] ORDER BY [Company];
    >
    >
    > "Ricter" wrote:
    >
    > > Row Source Type: Table/Query
    > > Row Source:
    > > SELECT DISTINCT [tblShipments].[ID], [tblShipments].[Company] FROM
    > > tblShipments ORDER BY [Company];
    > >
    > > I'm seeing as many instances of Company X in the dropdown list as there are
    > > instances of Company X in the table... What am I missing here?
    > >
    > > tia
     
  6. Ricter

    Ricter
    Expand Collapse
    Guest

    Ahh, I see. However, I get an empty dropdown when I remove ID. I need to
    make a lookup table I guess. Thanks.

    "Danny J. Lesandrini" wrote:

    > Your SQL Statement is looking for distinct ID/Name combos, so your DISTINCT
    > clause will return each unique pair.
    >
    > 123 Company X
    > 436 Company X
    > 854 Company X
    > etc.
    >
    > I'm assuming you don't need the ID, since it's a ShipmentID. Remove that from
    > your SQL and it should give you want you want.
    > --
    >
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    > "Ricter" <Ricter@discussions.microsoft.com> wrote ...
    > > Row Source Type: Table/Query
    > > Row Source:
    > > SELECT DISTINCT [tblShipments].[ID], [tblShipments].[Company] FROM
    > > tblShipments ORDER BY [Company];
    > >
    > > I'm seeing as many instances of Company X in the dropdown list as there are
    > > instances of Company X in the table... What am I missing here?
    > >
    > > tia

    >
    >
    >
     

Share This Page