Matching only not null

[Freshman]

Hi all,

In the data below I'd like to display only the ComboCode where both Cnt values are not null.
In the example only the 2 "Combo4" rows must display and nothing else.
"Is not null" as criteria for Cnt will not do the job

ComboCode Cnt
Combo1
Combo1 3
Combo1
Combo2 3
Combo2
Combo3 3
Combo3
Combo4 2
Combo4 3

I've also attached the db sample

Thanks
Pierre

 

[plog]

You need to make an aggregate query out of this (click the Summation/Sigma symbol). Then underneath the [Cnt] field, change the Group By to Min. Keep your Isnull criteria under it and you will have your results.

 

[Ranman256]

Make Q1 and pull all recs that have NULL.
Now make Q2 and use an OUTER join on Q1 to pull all items NOT in Q1.
Make Q3 from Q2 and count them.

 

[sneuberg]

If the rule is that the count of the ComboCode should equal the count of the Cnt. Then a query like the following would give you those ComboCodes

SELECT qryComboMatch.ComboCode
FROM qryComboMatch
GROUP BY qryComboMatch.ComboCode
HAVING (((Count(qryComboMatch.ComboCode))=Count([qryComboMatch].[Cnt])));

Then if this query was named qryAllCount then you could join it back to the qryComboMatch like

SELECT qryComboMatch.ComboCode, qryComboMatch.Cnt
FROM qryAllCount INNER JOIN qryComboMatch ON qryAllCount.ComboCode = qryComboMatch.ComboCode;

This would give you an output if you input had more counts like


ComboCode Cnt
Combo1
Combo1 3
Combo1
Combo2 3
Combo2
Combo3 3
Combo3
Combo4 2
Combo4 3
Combo4 4


The output would be

Combo4 2
Combo4 3
Combo4 4

If you could explain what you are doing perhaps we could give you a better solution. The DSum in the qryComboMatch makes me wonder if you have the structure and relationships are right.

 

[Freshman]

@plog - I tried your 1st but it gave me the opposite result, so I created a unmatched query based on that which gave me "Combo4" as the result.
This should do the trick - thanks for the reply.

@sneuberg - it is for a point of sale system to recognize combinations
See post #3 of http://www.access-programmers.co.uk/forums/showthread.php?t=290415

@Ranman - I think your solution is what I did in the comment to plog above. So thanks!