Dropdown list in combobox showing duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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];
 
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.
 
"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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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


Ricter said:
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
Click to expand...
Click to expand...
 
Back
Top