Query to Limit Combo Box Choices

[jimrockford]

I am working on a cigar dossier database used to track cigar inventory and reviews and have an issue with a query tied to a combo box that has me stumped. My knowledge of Access is limited, so this might be an easy fix.

Anyway, two tables and two queries are involved. The first table (Humidor) contains details about all of the cigars a person has purchased. The second table (Cigar_Dossier) contains reviews of cigars from the Humidor table. The first query (Cigars_Smoked_Query) returns the number of Cigar Dossier entries that correspond to each Humidor entry. And, finally, the query that is giving me trouble is tied to a combo box (Description) on the Cigar Dossier form used to select a cigar to review.

As written, the Description combo box pull down lists a selection of fields from all of the cigars in the Humidor. This is cumbersome because many of those entries are from old purchases where the cigars have already been smoked. So, I'm attempting to limit the pull down list to only cigars where the quantity remaining is greater than zero. My solution was to add a where clause to the query of [Quantity_Purchased] - [CountOfID] > 0. CountOfID is from the Cigars_Smoked_Query and is the number of Cigar Dossier entries for each Humidor entry.

That successfully limited the pull down list to show only cigars still in the Humidor inventory. However, it had the side effect of causing the Description field for all existing entries in the Cigar Dossier table to display as blank (except for those where some inventory still remained).

That, finally, leads to my question. How can I restrict the pull down list for the Description combo box without restricting the display of previous entries?

[ajetrumpet]

I am working on a cigar dossier database used to track cigar inventory and reviews and have an issue with a query tied to a combo box that has me stumped. My knowledge of Access is limited, so this might be an easy fix.

Anyway, two tables and two queries are involved. The first table (Humidor) contains details about all of the cigars a person has purchased. The second table (Cigar_Dossier) contains reviews of cigars from the Humidor table. The first query (Cigars_Smoked_Query) returns the number of Cigar Dossier entries that correspond to each Humidor entry. And, finally, the query that is giving me trouble is tied to a combo box (Description) on the Cigar Dossier form used to select a cigar to review.

As written, the Description combo box pull down lists a selection of fields from all of the cigars in the Humidor. This is cumbersome because many of those entries are from old purchases where the cigars have already been smoked. So, I'm attempting to limit the pull down list to only cigars where the quantity remaining is greater than zero. My solution was to add a where clause to the query of [Quantity_Purchased] - [CountOfID] > 0. CountOfID is from the Cigars_Smoked_Query and is the number of Cigar Dossier entries for each Humidor entry.

That successfully limited the pull down list to show only cigars still in the Humidor inventory. However, it had the side effect of causing the Description field for all existing entries in the Cigar Dossier table to display as blank (except for those where some inventory still remained).

That, finally, leads to my question. How can I restrict the pull down list for the Description combo box without restricting the display of previous entries?

perhaps some sql and data structures would prove useful here? pictures maybe? sql for sure.

[jimrockford]

As requested:

Cigar_Dossier Table

• ID - Autonumber - primary key
• Humidor_Selection - Number
• Date - Date/Time
• KJ_Rated - Number
• Notes - Text
• Bargain_Index - Text

Humidor Table

• ID - Autonumber - primary key
• Purchase_Date - Date/Time
• Quantity_Purchased - Number
• Description - Text
• Retailer - Text
• Price - Currency
• Length - Text
• Ring_Gauge - Text
• Band - Text
• Purchase_Type - Text
• Wrapper - Text
• Binder - Text
• Filler - Text
• Origin - Text
• Strength - Text
• Body - Text
• Manufacturer - Text
• Published Rating - Text
• Image_Path - Text
• Gifted? - Text

Cigar_Selection_Query - Row source for Humidor_Selection combo box on Cigar Dossier form:

SELECT Humidor.ID, Humidor.Description, [Quantity_Purchased]-[CountOfID] AS Expr1, Humidor.Retailer, Humidor.Price, Humidor.Length, Humidor.Ring_Gauge, Humidor.Purchase_Type, Humidor.Purchase_Date, Humidor.Quantity_Purchased, Humidor.[Band], Humidor.Wrapper, Humidor.Binder, Humidor.Filler, Humidor.Origin, Humidor.Strength, Humidor.Body, Humidor.Manufacturer, Humidor.Published_Rating, Humidor.Image_Path
FROM Humidor INNER JOIN Cigars_Smoked_Query ON Humidor.ID = Cigars_Smoked_Query.ID
WHERE ((([Quantity_Purchased]-[CountOfID])>0))
ORDER BY Humidor.Description;

Cigars_Smoked_Query - Returns Humidor.ID with count of matching Cigar_Dossier.ID records

SELECT Count(Cigar_Dossier.ID) AS CountOfID, Humidor.ID
FROM Humidor LEFT JOIN Cigar_Dossier ON Humidor.ID = Cigar_Dossier.Humidor_Selection
GROUP BY Humidor.ID;

Table relationships:



Here's what the Humidor form looks like:



And the Cigar Dossier form (before I made any changes):



After editing the row source for the Cigar Dossier Humidor_Selection field, I get a pull down list that is limited to cigars in inventory:



However, previous Cigar Dossier entries for cigars with a quantity of zero in the Humidor table have a blank Humidor_Selection field:



Previous entries for cigars where there are still some in inventory continue to display properly:



I hope that makes my situation more clear...