differ rates same state

[slimjen1]

All, using Access 2010. Looking at the table below, I need to query so that the results would identify only the CUST ID with different Rates in the same state. The query results in this case would be 1300 and 1300A. I tried to do a group and count but I get everything. Any suggestions please. I know it’s something easy and I’m just having a brain freeze.

Table
CustID 	  State	Rate
2500	 AZ         1.5
2500A    AZ         1.5
1300       MT        2.5
1300A    MT         1.5



 

[plog]

I need to query so that the results would identify only the CUST ID with different Rates in the same state

You have provided 4 unique CustID values, so what you expect to end with, doesn't make sense. There's only 1 1300 value and 1 1300A value in your starting data. It's impossible for them to be in your final query because it needs at least 2 records in your starting data to meet your criteria.

I think you have an issue bigger than the one you are posting about--you don't have your data properly structured.

 

[slimjen1]

I apologize. The table should look like the following:

CustID 	 Ext     State	Rate
2500	 2500	AZ         1.5
2500      2500A	 AZ         1.5
1300      1300	 MT        2.5
1300     1300A	MT         1.5

The custid is like the parent and the ext is related to the custid. I hope this helps.

 

[plog]

I would use a subquery. First get all the unique CustID, State, Rate permutations. Then on that query build another that counts all the CustID, State permutations. Anything with a Count > 1 will be what you want.

 

[slimjen1]

Got it. I'll try this. thanks