Stop Duplicates in Combo Box with 2 Criteria!

[Acipenser]

I have a database which controls the inventory for a store. When a "Species" come in, it gets a record which among other things stores how many were "Received". As the "Species" are shipped out, the number "Shipped" for each specific species is recorded. Each incoming shipment is kept separate with a "SpeciesID" and the number "Shipped" is related to this field.

On an species inventory form, I have a combo box that gets its choices from a table. The choices are from the field "Species". I can get the combo box to work properly, not display duplicate species, if I only use the field "Species" in the record source query builder and use the total: Group BY.

What I want to do is to only show those "Species" in the combo box whose "Shipped" total is less than "Received" (and not duplicate "Species" in the combo box). I can get the combo box to show only those "Species" whose "Shipped" is less than "Received", but it shows duplicate "Species" for each "SpeciesID" that has not been completely shipped.

How can I have a combo box that lists the "Species" once for all the "Species" that we have on hand ("Received" > "Shipped&quot?

Thanks in advance,

 

[snoopy75]

Paste this into the Row Source of your combo box:

SELECT DISTINCTROW [tablename].[Species] FROM [tablename] WHERE (([tablename].[Received])>([tablename].[Shipped])) ORDER BY [tablename].[Species];

Obviously, substitute the name of your own table for tablename.

 

[Acipenser]

That does not seem to work. I think because each row is distinct for a species in that it has a different number of "Received", "Shipped", or "SpeciesID". DistinctRow works fine if I only use one criteria. I run into the problem when using 2 criteria.

Any other suggestions?

 

[Acipenser]

I found a way to do it. It may not be the best way, or the simplest, but it works.

I made a query which selects the "Species" whose "Received" is greater than "Shipped". Easy enough. Now I populate the combo box with "Species", that are grouped by "Species", from the query. A no brainer.

I don't know why I did not think of this first off. Again thanks for you help.

 

[snoopy75]

That's the same idea I was trying to accomplish with the SQL statement, but I must have gotten it wrong somehow. Glad you figured it out.