I know the title isn't very desciptive but this is what I am trying to figure out.

Below is a query I am running that shows combo#, combo Seq, Combo Description, Item # and Item Description.

Combo# Combo Seq Combo Descrption Item# Description
7000059 1 Item 1 1 Not Avail
7000059 2 Item 2 2 Not avail
7000059 3 Item 3 3 Item 3 description


We have a store that has only a select few items that we carry for sale. Above you will see a combo deal example that I am trying to work with. Item 1 and 2 are not in the store but item 3 is. I am running a query with our stores items and all our combo deals. I am viewing all items in the combo and only those that match in the store items. If I do a one to one relationship item 3 still shows but no longer shows me items 1 and 2. My goal is to omit the entire combo from showing at all. Below is my current SQL.


SELECT tblCombo.CBCMBO AS [Combo#], tblCombo.[CBSEQ#] AS [Combo Seq], tblCombo.CADESC AS [Combo Descrption], tblCombo.CBPROD AS [Item#], tblItemMaster.Description, tblItemMaster.Size, tblItemMaster.PK, tblItemMaster.[Case_ List], tblItemMaster.Bottle_List, tblCombo.CBCASE AS Case_Buy, tblCombo.CBBTTL AS Btl_Buy, tblCombo.CBDLRO, tblCombo.CBFREE AS Free_Goods, tblCombo.CBNCCS AS [No_Charge Cs], tblCombo.CBNCBT AS [No_Charge Btl], tblCombo.CBDEPL, tblCombo.[Start Date] AS Start_Date, tblCombo.[End Date] AS End_Date, tblCombo.CAONPR, tblCombo.CALIST AS Lised, tblCombo.CABACO, tblCombo.CABTLC, IIf([tblCombo]![CAONPR]="Y","On Premise",IIf([tblCombo]![CAONPR]="N","Off Premise","Both")) AS Premise
FROM tblItemMaster RIGHT JOIN tblCombo ON tblItemMaster.[Item#] = tblCombo.CBPROD
GROUP BY tblCombo.CBCMBO, tblCombo.[CBSEQ#], tblCombo.CADESC, tblCombo.CBPROD, tblItemMaster.Description, tblItemMaster.Size, tblItemMaster.PK, tblItemMaster.[Case_ List], tblItemMaster.Bottle_List, tblCombo.CBCASE, tblCombo.CBBTTL, tblCombo.CBDLRO, tblCombo.CBFREE, tblCombo.CBNCCS, tblCombo.CBNCBT, tblCombo.CBDEPL, tblCombo.[Start Date], tblCombo.[End Date], tblCombo.CAONPR, tblCombo.CALIST, tblCombo.CABACO, tblCombo.CABTLC, IIf([tblCombo]![CAONPR]="Y","On Premise",IIf([tblCombo]![CAONPR]="N","Off Premise","Both"))
HAVING (((tblCombo.[End Date])>=Date()) AND ((tblCombo.CALIST)="Y") AND ((IIf([tblCombo]![CAONPR]="Y","On Premise",IIf([tblCombo]![CAONPR]="N","Off Premise","Both")))="Off Premise" Or (IIf([tblCombo]![CAONPR]="Y","On Premise",IIf([tblCombo]![CAONPR]="N","Off Premise","Both")))="Both"))
ORDER BY tblCombo.CBCMBO, tblCombo.[CBSEQ#];