Dropdown list in combobox showing duplicates

[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

 

[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];

 

[Danny J. Lesandrini]

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.

 

[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.

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];

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

 

[Guest]

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

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
(e-mail address removed)
http://amazecreations.com/datafast

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