INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Identiying records that meet a peculiar condition

Identiying records that meet a peculiar condition

(OP)
hello

I have a question that has me stumped.

Consider a large table as shown below...


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

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




RE: Identiying records that meet a peculiar condition

Please use the appropriate TGML tags when presenting your data.

  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 	
 
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.

RE: Identiying records that meet a peculiar condition

Try

CODE --> SQL

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

RE: Identiying records that meet a peculiar condition

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

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

Duane
Hook'D on Access
MS Access MVP

RE: Identiying records that meet a peculiar condition

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

I think that the table should be normalized (using this process in Excel FAQ68-5287: NORMALIZE Your Table using the PivotTable Wizard)...
]
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
 

So my result is 1002.

Then my SQL ...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Identiying records that meet a peculiar condition

(OP)
skip - nice answer!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close