Identiying records that meet a peculiar condition 2

[VickyC]

hello

I have a question that has me stumped.

Consider a large table as shown below...

[tt]
ID A B X1 X2 X3 X4 X5 X6
====================================================================
1000 1 6 1 6 6 10 6 1
1001 3 15 15 15 3 3 3 3
1002 13 8 13 8 13 8 8 13
1003 34 16 99 16 16 34 16 34 [/tt]
etc …

I need to be able to identify all ID values where the A and B values are repeated exactly 3 times each in the X columns. In this table sample, only ID = 1002 has this property. The X columns have exactly 3 values of 13, and 3 values of 8.

Much thanks for any ideas.
Vicky

 

[Andrzejek]

Please use the appropriate TGML tags when presenting your data.

[pre]
ID A B X1 X2 X3 X4 X5 X6
============================
1000 1 6 1 6 6 10 6 1
1001 3 15 15 15 3 3 3 3
1002 13 8 [blue]13 8 13 8 8 13[/blue]
1003 34 16 99 16 16 34 16 34
[/pre]
Don't you agree that this would be a lot easier to see?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PWise]

Try

Select ID
From tablename
Group by Id
Having sum(a+b)=Sum(X1+ X2 +X3 +X4+ X5 +X6)/3

 

[dhookom]

PWise,
I'm not sure why you are using Sum() when you reference only a single row. I think you could get a lot of false positives. Consider

[pre]
A B X1 X2 X3 X4 X5 X6
4 33 34 2 27 5 40 3[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

@PWise, I like your approach. However this row would also count as the sum is 7...
[pre]
1004 1 6 5 5 5 2 2 2
[/pre]

I think that the table should be normalized (using this process in Excel faq68-5287)...
[pre]]
ID A B Field Value
1000 1 6 X1 1
1000 1 6 X2 6
1000 1 6 X3 6
1000 1 6 X4 10
1000 1 6 X5 6
1000 1 6 X6 1
1001 3 15 X1 15
1001 3 15 X2 15
1001 3 15 X3 3
1001 3 15 X4 3
1001 3 15 X5 3
1001 3 15 X6 3
1002 13 8 X1 13
1002 13 8 X2 8
1002 13 8 X3 13
1002 13 8 X4 8
1002 13 8 X5 8
1002 13 8 X6 13
1003 34 16 X1 99
1003 34 16 X2 16
1003 34 16 X3 16
1003 34 16 X4 34
1003 34 16 X5 16
1003 34 16 X6 34
1004 1 6 X1 5
1004 1 6 X2 5
1004 1 6 X3 5
1004 1 6 X4 2
1004 1 6 X5 2
1004 1 6 X6 2
[/pre]

So my result is 1002.

Then my SQL ...

Select ID

From (

SELECT
  n1.ID
, count(n1.Value)

FROM `C:\Users\Skip\Documents\tt_tableCount.xlsx`.`Norm$` n1

Where n1.A=n1.Value

Group By n1.ID

Having count(n1.Value)=3 

union all

SELECT
  n1.ID
, count(n1.Value)

FROM `C:\Users\Skip\Documents\tt_tableCount.xlsx`.`Norm$` n1

Where n1.B=n1.Value

Group By n1.ID

Having count(n1.Value)=3 

)

Group by ID

Having count(ID)=2

..of course, your path and Sheet/Table Name might be different (my Sheet name is Norm)


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[VickyC]

skip - nice answer!