Schalton Schalton - 1 year ago 85
SQL Question

MS Access VBA/SQL check any of children

MS Access 2016

Goal, tag records based on child parameter; example:

Cat1 Cat2
A 1
A 2
A 5
B 3
B 4
B 1
C 3
C 2
C 5

Goal, within Group By check to see if 3 exists and tag w/ "FBWT"; result:

Cat1 Cat2
A Other

I've thought about creating a dictionary based on Cat1 that updates the value based on iterating through Cat1/2 pairs then prints "Other" or "FBWT" but this will run on millions of records so I'm ideally looking for a function that I can run in a query that opperates the groupby on Cat1.

Answer Source

Use conditional aggregation:

select cat1,
       min(iif(cat2 = 3, 'FBWT' 'Other'))
from t
group by cat1;

This uses MIN() as a bit of a short-cut. The strings are such that 'FBWT' < 'Other'.

A more formal way of doing this is more explicit:

select cat1,
       iif(sum(iif(cat2 = 3, 1, 0) > 0, 'FBWT', 'Other')
from t
group by cat1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download