Search code examples
ms-accessunioncriteria

Access Query Design - Complex Criteria?

I'm trying to figure out the best way to create a query.

Let's say I have two tables

enter image description here

In the query, I want the [Carrier] value to be 'IRT'. When [Co State] = [Lic State], I want the [Line] to display, but in the case of where SC and TN have two different line entries I need only the 'M' value to show.

A sample result would be like this: enter image description here

The table data is just a fake sample. The real data includes all 50 states and some [Lic State]s would have one value and a few would have two.

Is there an easy way to create these results with one query, or would it be best to create one query that omits the [Lic State] of 'SC' and 'TN', and one that only includes 'SC' and 'TN' that have a [Line] of 'M', and then take the SQL of both queries and join them via UNION?

Solution

  • I'm on the mobile, and did that just for fun, yeah.... it is not clean bur the idea is there.

    You need to use group by because you only want one record per state.

    Select first (carrier), first ( Costate), iif(count ( line)>1,"M", first (line)) from (select costate from t2 where licstate="irt") left join t1 on Costate=licstate group by costate