Drop down box shows querys duplicate values

[silversofttail]

I have a query of factories and their customers. My search form has a drop down box that performs the query by customer. The query is looking up the factory by customer: A, B, C. How can I have the drop down box just show the choices of A, B, C instead of A,A,A, B,B,C,C,C,C. I added Distinct to the SQL hoping that it would just show the one instance. Any help would be greatly appreciated.

Here is the SQL:

SELECT DISTINCT QrySupplier_Customer.Customer, tblFactory.FactoryName, tblFactory.FactoryStatus, QrySupplier_Customer.Category
FROM QrySupplier_Customer INNER JOIN tblFactory ON QrySupplier_Customer.FactoryID = tblFactory.FactoryID
WHERE (((QrySupplier_Customer.Customer) Like [Forms]![frmSearch].[ComboActiveFactories] & "*") AND ((tblFactory.FactoryStatus)="Active"))
ORDER BY QrySupplier_Customer.Customer;

 

[John Big Booty]

Have a look at a Totals Query and use the Group By option to group like values.

 

[silversofttail]

Hi John,

I've tried and tried but cannot figure out how to use the Group By option so that the drop down list just shows 1 iteration of the customer name. My list still shows all instances of the customer name.

SELECT QrySupplier_Customer.Customer, tblFactory.FactoryName, tblFactory.FactoryStatus, QrySupplier_Customer.Category
FROM QrySupplier_Customer INNER JOIN tblFactory ON QrySupplier_Customer.FactoryID=tblFactory.FactoryID
GROUP BY QrySupplier_Customer.Customer, tblFactory.FactoryName, tblFactory.FactoryStatus, QrySupplier_Customer.Category
HAVING (((QrySupplier_Customer.Customer) Like Forms!frmSearch.ComboActiveFactories & "*" Or (QrySupplier_Customer.Customer)="Wal_mart" Or (QrySupplier_Customer.Customer)="Lowes" Or (QrySupplier_Customer.Customer)="Home Depot") And ((tblFactory.FactoryStatus)="Active"))
ORDER BY QrySupplier_Customer.Customer;

I don't know what else to do. Have I missed something?

 

[John Big Booty]

Can you post a copy of your DB? '03 version for preference certainly no newer than '07.

 

[silversofttail]

Unfortunately, I cannot post a copy of the db because it contains proprietary data.

 

[silversofttail]

I have other drop down boxes on my form with similar duplicate fieldS (eg Country) but they just show one instance. The only difference I can think of is that the query below is pulling from two tables.

I've modified the SQL to remove the specific criteria and to Group by Customer:

SELECT QrySupplier_Customer.Customer, tblFactory.FactoryName, tblFactory.FactoryStatus, QrySupplier_Customer.Category
FROM QrySupplier_Customer INNER JOIN tblFactory ON QrySupplier_Customer.FactoryID=tblFactory.FactoryID
WHERE (((QrySupplier_Customer.Customer) Like Forms!frmSearch.ComboActiveFactories & "*") And ((tblFactory.FactoryStatus)="Active"))
GROUP BY QrySupplier_Customer.Customer;

When I try to run I get the message:
You tried to execute a query that does not include the specified expression 'FactoryName' as part of an aggregate function.

I do not know what that means.

Any other thoughts?
Thanks so much!