Hiding Duplicate Values in Drop Downs

[helpmeee]

Hey All,

I'm having a LOT of trouble with this. I have a query that contains PO Number, Supplier, Last Name, First Name and Value. I wanted to create a form that contained unbound drop down menus so I could run reports on individual components of my query (ie: if I wanted to see all of the Suppliers that have values under the name AIG, I could go to my Supplier drop down, select "AIG" and have a report run.)

The problem: I have several fields that are duplicates in the table that this query is pulling from. If I put "unique" in the table, I will lose data because the Value and First/Last names are different.

Is there a way I can modify my drop down menus to ONLY contain unique values without losing data? Thanks so much!

 

[Uncle Gizmo]

I think you will find this thread here useful.

 

[helpmeee]

The videos were not helpful with what I needed to accomplish I did learn something from them though!

To give everyone a better idea...

I have this:

And don't want duplicates appearing.. but also don't want to lose any information.

 

[boblarson]

1. If you are pulling this data like this then you haven't normalized your data properly. You should have a vendor table with ONE entry for each. Then you store the ID in the table where you are adding your records. You don't keep the text and then use the same table as the lookup.

2. If you insist on doing it this way, then you would go into the combo's ROW SOURCE property and, if you have a table listed, create a query to be the row source instead. Then you use GROUPING to group on the name so it shows up once.


If you have a table as the row source and aren't sure how to change to a query:
http://www.btabdevelopment.com/main...dsourcefromtabletoquery/tabid/75/Default.aspx
(this sample is actually for a form/report recordsource, but the same thing applies to a combo/listbox ROW SOURCE property)

If you are not sure about the grouping button:

 

[helpmeee]

1. If you are pulling this data like this then you haven't normalized your data properly. You should have a vendor table with ONE entry for each. Then you store the ID in the table where you are adding your records. You don't keep the text and then use the same table as the lookup.

2. If you insist on doing it this way, then you would go into the combo's ROW SOURCE property and, if you have a table listed, create a query to be the row source instead. Then you use GROUPING to group on the name so it shows up once.


If you have a table as the row source and aren't sure how to change to a query:
http://www.btabdevelopment.com/main...dsourcefromtabletoquery/tabid/75/Default.aspx
(this sample is actually for a form/report recordsource, but the same thing applies to a combo/listbox ROW SOURCE property)

If you are not sure about the grouping button:

The form is pulling information from a query I have based off of a table. I figured the simplest way would be to filter the query somehow so on the form it would show the appropriate headings. My Row Source on the form (for each individual drop down) already contains its own bit of information: SELECT [Drop Downs].[PO NUMBER] FROM [Drop Downs] WHERE ((([Drop Downs].[PO NUMBER])=[Drop Downs]![PO NUMBER]));

Where do I go from here?

 

[boblarson]

The form is pulling information from a query I have based off of a table. I figured the simplest way would be to filter the query somehow so on the form it would show the appropriate headings. My Row Source on the form (for each individual drop down) already contains its own bit of information: SELECT [Drop Downs].[PO NUMBER] FROM [Drop Downs] WHERE ((([Drop Downs].[PO NUMBER])=[Drop Downs]![PO NUMBER]));

Where do I go from here?

SELECT [Drop Downs].[PO NUMBER] FROM [Drop Downs] GROUP BY [PO NUMBER];

 

[helpmeee]

SELECT [Drop Downs].[PO NUMBER] FROM [Drop Downs] GROUP BY [PO NUMBER];

You just made my entire day
Now, even though I have duplicate values in the PO Number but different values in the Supplier Name, no values will be lost by selecting 'group by'?
THANK YOU!

 

[boblarson]

no values will be lost by selecting 'group by'?

That is correct. You will get one value for each PO Number.